Find two fields NOT IN

[bmacbmac]

Hello,

I have two tables with the same fields:

mytable1.bookno mytable2.bookno
mytable1.pageno mytable2.pageno

I would like to find the bookno & pageno from mytable1 where they do not exist in mytable2. The bookno/pageno together.

For example, Book 74 page 105. Book 74 will exist in both tables. Page 105 will also exist in both tables (from different books). But I want to find out that book 74 page 105 is in mytable1 but not in mytable2.

Something like this:

select bookno, pageno from mytable1
where bookno, pageno not in (select sbookno, spageno from mytable2)

But this is just my juvenile attempt.

I also ran:

select mytable.bookno, mytable.pageno, mytable.bookno, mytale.pageno
from mytable
join mytable2 on (mytable1.bookno = mytable1.pageno and mytable2.bookno = mytable2.pageno)
order by mytable2.booktype, mytable2.bookno, mytable2.pageno

So I can see all of the NULL entries for mytable2. I just cant figure out how to get ONLY the null entries, or, just those items in mytable1 that don't exist in mytable2

 

[markros]

Please see this link for 6 ways of doing it

http://blogs.lessthandot.com/index..../the-ten-most-asked-sql-server-questions--1#4

 

[markros]

So, your query in SQL Server 2005 and up would be

select BookNo, PageNO from myTable1
EXCEPT
select BookNo, PageNo from myTable2

Very simple and nice syntax.

 

[bmacbmac]

Thanks markros. Unfortunately I am using SQL 2000. Forgot to mention that AGAIN.

I just tried:

select BookNo, PageNO from mytable1
where not exists (select BookNo, PageNo from mytable2)

but I get 0 rows.

 

[markros]

I don't know if you visited the WiKi post I mentioned - it shows the problem in details.

select BookNo, PageNO from mytable1 T1
where not exists (select BookNo, PageNo from mytable2 T2 where T2.BookNo = T1.BookNo and T2.PageNo = T1.PageNo)

This is called correlated subquery.

 

[markros]

Alternative solution is in using LEFT JOIN and check for IS NULL condition, e.g.

select mytable.bookno, mytable.pageno
from mytable
LEFT JOIN mytable2 on (mytable1.bookno = mytable1.pageno and mytable2.bookno = mytable2.pageno)
WHERE myTable2.BookNo IS NULL
order by mytable1.bookNo, myTable2.PageNo

This is only to show records that are in MyTable, but not in myTable2

If you want to show all unique records, then use FULL OUTER JOIN and check for NULL for BookNo for both tables.

 

[bmacbmac]

Thanks Markros. The code:

select BookNo, PageNO from mytable1 T1
where not exists (select BookNo, PageNo from mytable2 T2 where T2.BookNo = T1.BookNo and T2.PageNo = T1.PageNo)

worked perfectly!

 

[markros]

Ok, did you understand it? And you can also mark a helpful response with a purple star - this site uses purple, my favorite site uses gold and MSDN/ASP.NET forums use green.