Find orphaned records in two related tables

[lyudmila]

I have two related tables, Invoice Header and Invoice Detail. They have two key fields: Vendor# and Invoice#. What is the best way to find orphaned records - records in Detail table that have no match in Header table?

 

[ClaireHCM]

select * from InvoiceHeader a full outer join InvoiceDetail b on a.Vendor# = b.Vendor# and a.Invoice# = b.Invoice#

In the result you would see the null rows either from InvoiceDetail or from InvoiceHeader where you could identify the orphand records

 

[SemperFiDownUnda]

to expand on that do

SELECT ....
  FROM InvoiceHeader IH
  FULL OUTER JOIN InvoiceDetail ID
    ON IH.Vendor# = ID.Vendor#
   AND IH.Invoice# = ID.Invoice#
 WHERE IH.Invoice# IS NULL
    OR ID.Invoice# IS NULL

The WHERE clause will filter out the records that are not orphaned leaving you with just the parent and child records that have no relationship.

 

[lyudmila]

THANK YOU VERY MUCH! THIS WILL WORK