Find matching records in same table 1

[jebenson]

Hello all,

I need a little help designing a query that will find a record's matching record in the same table. Here's the setup:

2 tables - Document and Transaction

In Document I have DocumentNumbers and DocIDs

In Transaction I have a DocID field that matches back to the Document table, a MatchDocNum field and a MatchDocID field.

For any given document that is not a matching document for anther document, the MatchDocNum and MatchDocID fields will hold the same data as the DocumentNumber and DocID fields. For example:

Document table:

DocNum DocID
B0001 0001
C0001 0002
D0001 0003

Transaction table:
DocID MatchDocNum MatchDocID
0001 B0001 0001
0002 C0001 0002
0003 D0001 0003

However, for a document that is a matching document for another document, the MatchDocID and MatchDocNum will point to the original document:

Document table:

DocNum DocID
B0001 0001
C0001 0002
D0001 0003
E0001 0004 <----matching doc

Transaction table:
DocID MatchDocNum MatchDocID
0001 B0001 0001
0002 C0001 0002
0003 D0001 0003
0004 E0001 0004
0004 C0001 0002 <--matches C0001
0004 B0001 0001 <--matches B0001

Note that a document can be a matching document for more than one other document.

What I need is a query that will provide me with a document number, and the matching document number for that document:

DocNum MatchDocNum
B0001 E0001
C0001 E0001
D0001
E0001

Note that is a document does not have a matching document, I still need it to show up in the results.

Here's what I've tried so far:

SELECT Doc.DocNum as DocNum, MDoc.DocNum AS MatchDocNum
FROM Document Doc INNER JOIN
(SELECT DISTINCT Doc.DocNum, Doc.DocID
FROM Document Doc, Transaction Trans
WHERE Doc.DocID = Trans.MatchDocID) MDoc
ON Doc.DocID = MDoc.DocID

But this is not giving me what I want...I'm getting something like this:

DocNum MatchDocNum
B0001 B0001
C0001 C0001
D0001 D0001
E0001 E0001

Any ideas?

Oh, BTW...I did not design this database. It is part of a commercial product used in my office, and I have been tasked with producing this report. So, I cannot change the DB structure, although I wish I could....

TIA,
JEB

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[r937]

select Doc.DocNum as DocNum
     , MDoc.DocNum AS MatchDocNum
  from Document Doc 
left outer 
  join Transaction Trans
    in Doc.DocID = Trans.DocID
   and Trans.DocID <> Trans.MatchDocID

r937.com | rudy.ca

 

[PHV]

SELECT A.DocNum, M.DocNum AS MatchDocNum
FROM Document A LEFT JOIN (
SELECT MatchDocID, D.DocNum
FROM Transaction T INNER JOIN Document D ON T.DocID = D.DocID
WHERE MatchDocID <> T.DocID
) M ON A.DocID = M.MatchDocID
ORDER BY 1, 2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886