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.

Any help is appreciated.

Timothy,

See "Exists" keyword and "Using Outer Joins" in BOL.

select a.*
from PartsShipped as a
where not exists (
select *
from PartsConsumed as b
where b.SerialNumber = a.SerialNumber
)
go

select a.*
from PartsShipped as a left join PartsConsumed as b
on a.SerialNumber = b.SerialNumber
where b.SerialNumber is null
go


AMB

Show quoteHide quote

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.

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 SAQL-2005, you can also use the EXCEPT operator.

SELECT *  FROM PartsShipped
EXCEPT
SELECT * FROM PartsConsumed;

Urgent: Help on query
Copy SQL ResultSet to Variable
User-defined Select list
PLEASE HELP with Query
bulk insert from remote machine fails