Question: I've got the following tables:

**Table1 {ArticleNo (int), ArtDescription (string) }**

**Table2 { ArticleNo (int), Year (date) }**

Table1.ArticleNo is a primary key.
Table2.ArticleNo is a foreign key referenced to table1.ArticleNo

It's difficult to explain what I want to query, so here a short example:

---

**Table1**

```
(1,Desk)
(2,Chair)
(3,Ruler)

```

**Table2**

```
(1,2000)
(1,2000)
(2,2001)

```

The query should return:

```
1 Desk 2001
2 Chair 2000
3 Ruler 2000
3 Ruler 2001

```

All articles which are not sold (or whatever) in all years (all years from table2).

---

I hope you understand my example - the query seems to be very complex. Here my approach to a solution:

```
SELECT table1.ArticleNo,table1.ArtDescription,table2.Year
FROM table1
JOIN table2
ON table1.ArticleNo=table2.ArticleNo
WHERE NOT table1.ArticleNo IN (SELECT table2.Year FROM table2);

```

I tried lots of different things.. I hope you can help me!

Answer: 
You could use a `cross join` to create a list of all item+year combinations. Then you could filter the rows without sales with a `not exists` condition:

```
select  *
from    t1 items1
cross join    
        (
        select  distinct year
        from    t2 sales1
        ) sales2
where   not exists
        (
        select  *
        from    t2 sales3
        where   sales3.ItemId = items1.ItemId
                and sales3.Year = sales2.Year
        )

```
```
SELECT DISTINCT table1.ArticleNo,table1.ArtDescription,table2.Year
    FROM table1 CROSS JOIN table2 
    WHERE table1.ArticleNo != table2.ArticleNo order by table1.ArticleNo;

```