Inverted SQL query

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 开发者_如何学Chope you can help me!

SELECT  t1.*, t2.year
FROM    t1
CROSS JOIN
        (
        SELECT  DISTINCT year
        FROM    t2
        ) t2
WHERE   (t1.id, t2.year) NOT IN
        (
        SELECT  t2.id, t2.year
        FROM    t2
        )

Create an index on t2 (year, id) (in this order) for the query to work fast.

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
        )

There are a bunch of ways of doing this. Two examples:

select
  t1.ArtDescription,
  y.Year
from
  Table1 t1
  join (
    select distinct
      t2.Year
    from
      Table2 t2
  ) y on 1=1
where
  not exists (
    select
      1
    from
      Table2 tx2
    where
      tx2.ArticleNo = t1.ArticleNo and tx2.Year = y.Year)

Oracle (SQL Server can do the same thing, use EXCEPT instead of MINUS):

select
  t1.ArtDescription,
  y.Year
from
  Table1 t1
  join (
    select distinct
      t2.Year
    from
      Table2 t2
  ) y on 1=1
MINUS
select
  t12.ArtDescription
  t22.Year
from
  Table1 t12
  join Table2 t22 on t12.ArticleNo = t22.ArticleNo

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