Weird Requirement... Multiple Left Joins? Am I missing something?

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?? BigGrin

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

Doc Tbl		GR Tbl				GE tbl				Ref tbl		

DocNums		Docnum	GR	Seq		DocNum	GE	Seq		DocNum	Ref	Seq
1		1	A	1		1	A	1		1	A	1
		1	B	2		1	B	2		1	B	2
						1	C	3		1	C	3
										1	D	4
										1	E	5

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:

Docnum	GR	GR_Seq	GE	GE_Seq
1	A	1	A	1
1	B	2	B	2
1	NULL	NULL	C	3

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:

Docnum	GR	GR_Seq	GE	GE_Seq	Ref	Ref_Seq
1	A	1	A	1	A	1
1	B	2	B	2	B	2
1	NULL	NULL	C	3	C	3
1	NULL	NULL	NULL	NULL	D	4
1	NULL	NULL	NULL	NULL	E	5

Create table Doc (
DocNum nvarchar(15) NOT NULL
);

Insert into Doc (DocNum)
Values ('1');

------------------------------
Create Table GR (
DocNum nvarchar(15) NOT NULL,
GR nvarchar(15) NOT NULL,
Seq int NOT NULL
)

Insert into GR (DocNum, GR, Seq) Values (1,'A',1)
Insert into GR (DocNum, GR, Seq) Values (1,'B',2)

--------------------------------
Create Table GE (
DocNum nvarchar(15) NOT NULL,
GE nvarchar(15) NOT NULL,
Seq int NOT NULL
)

Insert into GE (DocNum, GE, Seq) Values (1,'A',1)
Insert into GE (DocNum, GE, Seq) Values (1,'B',2)
Insert into GE (DocNum, GE, Seq) Values (1,'C',3)

--------------------------------------------------

Create Table Ref (
DocNum nvarchar(15) NOT NULL,
Ref nvarchar(15) NOT NULL,
Seq int NOT NULL
)

Insert into Ref (DocNum, Ref, Seq) Values (1,'A',1)
Insert into Ref (DocNum, Ref, Seq) Values (1,'B',2)
Insert into Ref (DocNum, Ref, Seq) Values (1,'C',3)
Insert into Ref (DocNum, Ref, Seq) Values (1,'D',4)
Insert into Ref (DocNum, Ref, Seq) Values (1,'E',5)

select * from Doc
select * from GR
Select * from GE
select * from Ref

WITH AllSeq AS (
SELECT Seq FROM GR
UNION
SELECT Seq FROM GE
UNION
SELECT Seq FROM Ref)

SELECT d.DocNum,
gr.GR,
gr.Seq AS GR_Seq,
ge.GE,
ge.Seq AS GE_Seq,
rf.Ref,
rf.Seq AS Ref_Seq
FROM Doc d
CROSS JOIN AllSeq sq
LEFT OUTER JOIN GR gr ON gr.DocNum = d.DocNum
AND gr.Seq = sq.Seq
LEFT OUTER JOIN GE ge ON ge.DocNum = d.DocNum
AND ge.Seq = sq.Seq
LEFT OUTER JOIN Ref rf ON rf.DocNum = d.DocNum
AND rf.Seq = sq.Seq
ORDER BY sq.Seq;

select Doc.DocNum, GR.GR, GR_Seq = GR.Seq, GE.GE, GE_Seq = GE.Seq, Ref.Ref, Ref_Seq = Ref.Seq
from Doc
cross join ( values (1),(2),(3),(4),(5),(6),(7),(8),(9) ) N (n)
left outer join GR on GR.DocNum = Doc.DocNum and N.n = GR.Seq
left outer join GE on GE.DocNum = DOC.DocNum and N.n = GE.Seq
left outer join Ref on Ref.DocNum = DOC.DocNum and N.n = Ref.Seq
where Ref.Ref is not null
order by Doc.DocNum, N.n

SELECT COALESCE(dbo.GR.DocNum, dbo.GE.DocNum, dbo.Ref.DocNum) AS DocNum,
GR,
dbo.GR.Seq AS GR_Seq,
GE,
dbo.GE.Seq AS GE_Seq,
Ref,
dbo.Ref.Seq AS Ref_Seq
FROM dbo.GR
FULL OUTER JOIN dbo.GE
ON dbo.GR.DocNum = dbo.GE.DocNum
AND dbo.GR.Seq = dbo.GE.Seq
FULL OUTER JOIN dbo.Ref
ON dbo.GE.DocNum = dbo.Ref.DocNum
AND dbo.GE.Seq = dbo.Ref.Seq
OR dbo.GR.DocNum = dbo.Ref.DocNum
AND dbo.GR.Seq = dbo.Ref.Seq;