Find items not in another database... based on grouping

[bmacbmac]

Hello. I have two databases. DB1 and DB2. Both containing tables DocInfo and PartyInfo. With the following info

DB1..docinfo.recordid, DB1..docinfo.Docno, DB1..partyinfo.recordid, DB1..partyinfo.name
1,12345, 1, SMITH
2,12346, 2, JONES
2, ANDERSON
2, MATTHEWS
3,12347 3, WILLIS
3, MATTHEWS
3, KIMMEL



DB2..docinfo.recordid, DB2..docinfo.Docno, DB2..partyinfo.recordid, DB2..partyinfo.name
1,12345, 1, SMITH
2,12346, 2, JONES
2, MATTHEWS
3,12347 3, WILLIS
3, MATTHEWS
3, KIMMEL

Is there a way I can find, for each document number, a name that exists in DB1, but not in DB2?

For example, IN DB1, recordid 2, docno 12346 has JONES, ANDERSON, MATTHEWS

DB2 for the same recordid and docno only contain JONES and MATTHEWS.

I would like to know that ANDERSON is missing for this particular recordid or docno.

Any ideas? Much appreciated.

 

[bobchuckjim]

Hi, not 100% sure if you want to find missing records for document number or record id or both so will assume both.

You can use a left join from db1 to db2 on recordid and docno and then add a where clause to identify those rows that are missing in db2

<pseudo code>
select DB1..partyinfo.name
from db1
left join
DB2 on
DB2.recordid = DB1.recordid
and DB2.docno = DB1.docno
where DB2.recordid is null