CptCrusty1 (8/27/2012)

---

Ok, so I think I should know the answer to this but I'm getting nothing but what looks like a cartesian product... yuk.

I have 1 table that has a list of master document numbers... for the sake of argument, there is 1 doc num, 1 record.

There are 3 tables that have values that relate to the DocNum in the Doc Tbl; however, they all don't totally relate to each other. Confused yet?? 😀

The DocNums are related. However, the Seqence numbers in the other tables determine how they should appear in the final results table.

Doc TblGR TblGE tblRef tbl

DocNumsDocnumGRSeqDocNumGESeqDocNumRefSeq

11A11A11A1

1B21B21B2

1C31C3

1D4

1E5

If I ignore the master list of Doc Numbers in the Doc tbl and Left Join GR to GE on DocNum and Seq, I get:

DocnumGRGR_SeqGEGE_Seq

1A1A1

1B2B2

1NULLNULLC3

It starts getting whonky if I add the third table because the Sequence number relates to the other two tables. There is no gaurantee that the quantity of sequence numbers is going to be the same in each table. GE could have 5 in the sequence, while GR has 1, and REF has 15. What's REALY chunking up the whole thing is if I try to refer it back to the master list of DocNums. When I try to do that the whole thing blows up and I get what looks to be a cartesian product...

My end result should look like this:

DocnumGRGR_SeqGEGE_SeqRefRef_Seq

1A1A1A1

1B2B2B2

1NULLNULLC3C3

1NULLNULLNULLNULLD4

1NULLNULLNULLNULLE5