Junction Table to relate a table back to itself? 1

[pismojim]

It seems like I'm using a Junction Table to relate a table back to itself.

I'm working with a set of documents (correspondence) that are related to each other. Basically, "this letter is related to that one." A given document can be related to none or many other documents. I made a table with information about each document with DocID(PK). I created a junction table with DocID(PK)+ Item#(PK) and a RelatedItem field. I put the DocID of any related documents in RelatedItem field.

I can query to select a list of the related documents for a given document. It returns a list of the DocIDs of the related documents. That's almost what I want.

Unfortunately I haven't figured out how to use these DocIds to display other information (fields) in the document table. It feels like I'm missing something simple, but I'm just not seeing it.

Am I on the right track? Any suggestions?

Thanks,
Jim

 

[PHV]

Something like this ?
SELECT D.*, J.*, R.*
FROM (tblJunction J INNER JOIN tblDocuments D ON J.DocID = D.DocID)
INNER JOIN tblDocuments R ON J.RelatedItem = R.DocID

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

 

[pismojim]

Yep, that's it.

I'm off to learn more about alias and self-join.

Thanks for the nudge!