Subselect?

pokermagic · December 11th, 2003, 02:01 PM

I have two tables, say tableA and tableB. TableA has 515 records and 513 of them were automatically dumped into tableB. I'm trying to find out which records from tableA did not make it into tableB. Here's part of my sql below..

SELECT Test.checknumber from Test, Approved_Details_Table WHERE Test.checknumber = Approved_Details_Table.Check_Number

This will get me the 513 records but I don't want to sort through the 515 to see what the 2 are... do I create a subselect to find this out?

Thanks.

Jonax · December 11th, 2003, 02:06 PM

SELECT * FROM Test A WHERE NOT EXISTS(SELECT * FROM Approved_Details_Table B WHERE A.Check_Number = B.Check_Number)

pokermagic · December 11th, 2003, 02:21 PM

Perfect... Thanks

Jeff Mason · December 11th, 2003, 02:48 PM

Another way to do this is via an OUTER JOIN:

Code:

SELECT TableA.*
    FROM TableA LEFT JOIN TableB ON TableA.CheckNumber=TableB.CheckNumber
WHERE TableB.CheckNumber IS NULL;

This works because the OUTER JOIN preserves all the rows of TableA, and sets the corresponding values of TableB to NULL if there is no match based on the CheckNumber. The WHERE clause then selects only those (missing from TableB) rows.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com