Finding table entries that do not match

[mburke]

I want to find entries in one table that are not in the other. But this does not work...

SELECT DISTINCT a.*
FROM Invoice2 a INNER JOIN
Invoice1 b ON a.custNum <> b.InvoiceNum
ORDER BY a.custNum


I want all the Invoice2.custNum records returned that are not in Invoice1

[vonarxma]

try this:

select * from Invoice2 a where a.custNum not in (select b.InvoiceNum as custNum from Invoice1 b)

perhaps "as custNum" is not neccessery

[dinesh123]

select custNum from Invoice2 where custNum not in (select InvoiceNum from Invoice1 )

But I think u need custnum at one time

therefore

select DISTINCT custNum from Invoice2 where custNum not in (select InvoiceNum from Invoice1 )

[antares686]

Another way is


SELECT
DISTINCT a.*
FROM
Invoice2 a
LEFT JOIN
Invoice1 b
ON
a.custNum = b.InvoiceNum
WHERE
b.InvoiceNum IS NULL
ORDER BY
a.custNum


As far as which is better you have to test to see for sure.

[Thread1]

..Or this one:D

SELECT DISTINCT * FROM Invoice2 a
WHERE NOT EXISTS (SELECT InvoiceNum FROM Invoice1 WHERE InvoiceNum = a.custNum)
ORDER BY custNum

[mburke]

Thanks guys, this really helps a lot :) :)