Help with reconciling data in two tables

SQL Apprentice Question
I have two tables - PartsShipped and PartsConsumed. Each table has
three columns - Processdate (a date/time stamp), PartNumber, and
SerialNumber.

I need a simple query that will show me the 3 bits of info for all
serial numbers that exist in the PartsShipped table, but not in the
PartsConsumed table.


I know that this shouldn't be too hard, but I cannot for the life of me
get it to work.

Celko Answers

>> Each table has three columns - Processdate (a date/time stamp), PartNumber, and SerialNumber. <<


Without a quantity? A little strange. Please post DDL, so that people
do not have to guess what the keys, constraints, Declarative
Referential Integrity, data types, etc. in your schema are. Sample data
is also a good idea, along with clear specifications.


>> I need a simple query that will show me the 3 bits of info for all serial numbers that exist in the PartsShipped table, but not in the PartsConsumed table. <<


SELECT process_date, part_nbr, serial_nbr
FROM PartsShipped AS S
WHERE NOT EXISTS
(SELECT *
FROM PartsConsumed AS C
WHERE C.serial_nbr = S.serial_nbr);

Ifyou have SQL-2005, you can also use the EXCEPT operator.


SELECT * FROM PartsShipped
EXCEPT
SELECT * FROM PartsConsumed;