sql server mseq: I have two tables each with concatenated key - ContractNo, SerialNo, JobNo, PNo

I would like to find out missing records in the other table, something like

select ContractNo, SerialNo, JobNo, PNo from table a
where not exists ( ContractNo, SerialNo, JobNo, PNo from table b)

when I do the query with exists in it, it shows me matching records ( I
think so as most of the recs are matching, so I can't say for sure)
however, when I change it to not exists, it doesn't .

Thank you,
-Me

Try this query:

SELECT
a.ContractNo
, a.SerialNo
, a.JobNo
, a.PNo=20
FROM TableA a
LEFT JOIN TableB b
ON ( a.ContractNo =3D b.ContractNo
AND a.SerialNo =3D b.SerialNo
AND a.JobNo =3D b.JobNo
AND a.PNo =3D b.PNo
)
WHERE b.ContractNo IS NULL

This will locate rows in TableA without a counterpart in TableB. Change =
it to a RIGHT JOIN and WHERE a.ContractNo IS NULL to find any rows that =
are in TableB without counterparts in TableA.

And, stay tuned, you may get a better offer.

--=20
Arnie Rowland, YACE*=20
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam


[quoted text, click to view]