Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Weird Requirement... Multiple Left Joins? Am I missing something? Expand / Collapse
Author
Message
Posted Monday, August 27, 2012 2:45 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:00 PM
Points: 183, Visits: 322
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 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




My recordsets could have 1000-1000000 records on any given day...

I feel like I should be able to do this; however, I feel like I've overlooked something basic.
Any ideas anyone? Bueller?

Thanks
Crusty.
Post #1350594
Posted Monday, August 27, 2012 6:37 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:03 PM
Points: 3,590, Visits: 5,098
You've given us expected results, which is a good thing. Some people like me visualize solutions most easily when this is provided.

However you have not provided DDL for your tables, nor INSERTs (setups) for your sample data. And the format that you posted them in would be a pain to convert.

I think if you do this, someone is going to be able to make short work of your question.

Remember we're all volunteers here.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1350654
Posted Monday, August 27, 2012 8:36 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:00 PM
Points: 183, Visits: 322
I will provide some DDL for the tables in the morning... Thanks for the positive reply... :)
Post #1350664
Posted Monday, August 27, 2012 9:42 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 3:11 AM
Points: 713, Visits: 337
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 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







Hi you are joining "DocNums" as in first part (query) there is no repeat of the value of "DocNum" 1


in second query there is repeat of doc no .

if there is repeat of joining data returns multiple record.

As it works row by row...
Post #1350678
Posted Tuesday, August 28, 2012 7:57 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:00 PM
Points: 183, Visits: 322
@ Sandeep,

I'm sorry, but I'm not quite sure what it is you're telling me; however, in regards to the data, it's correct. Let me get into some detail.

This is the end result of an ETL process using some pretty mixed up text files. Lets say I have one record in a file that is Pipe-Delimited with double quotes indicated text.


Using the examples in previous post, when I receive the information I have a text file with the following columns:

DocNum
GR
GE
REF

The raw data for the examples provided in the original post:

1|"A,B"|"A,B,C"|"A,B,C,D,E,F"

The providor of the data has essentially combined multiple rows of data into 1 row. For the GR (Second column), "A,B" is actually 2 rows of data broken by the comman. It should actually look like:
Docnum	GR
1 A
1 B

The Sequence number is an attempt at recombining the data and is not apart of the original data. The process in place now uses multiple steps to achieve individual tables for the broken out data exactly as it's shown in the previous examples. The problem is that they combine it all back together with Cartesian joins creating a massive landing table.

I've consolidated the cleaning of the data into a single procedure; however, I'm trying to recombine the data so that it's not a cartesian join. All the information in the single record pertains to DocNum 1; however, the end result doesn't necessarily have to be totally organized ABC, etc. Every value needs to be in a record with DocNum 1.

I know this is a very confusing process. The final destination for the data is totally wierd, and will make a dba have a heart attack; however, there is a method to the madness and it works. I'm trying to streamline the process of getting their data to the final step. Also, the example I provided is significantly simplified... but it's the root of the problem.

I'm trying to make it clearer.... .. sorry.

Crusty.

Post #1350956
Posted Tuesday, August 28, 2012 9:24 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:00 PM
Points: 183, Visits: 322
dwain.c (8/27/2012)
However you have not provided DDL for your tables, nor INSERTs (setups) for your sample data.
Remember we're all volunteers here.



Ok Volunteers... here's your script as promised... I know, I'm slow...


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

OH GREAT WIZARDS OF UBER-CODE! HEAR MY SCHREAKING AND BID ME GOOD TIDINGS!!!
Post #1351038
Posted Tuesday, August 28, 2012 9:49 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 3:11 AM
Points: 713, Visits: 337
hiC rusty hop u will get it by reading below code



  • 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 Ref
    left join GE
    on ref.DocNum=GE.DocNum


  • See the output of bold part of query
    as Ref have 5 values "1" in DocNum

    and GE have 3 values "1" in DocNum


    so output will have 15 rows as join behave row by row.
    Post #1351065
    Posted Tuesday, August 28, 2012 10:04 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Tuesday, April 08, 2014 6:13 AM
    Points: 1,694, Visits: 19,550


    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;



    ____________________________________________________

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

    Never approach a goat from the front, a horse from the rear, or a fool from any direction.
    Post #1351072
    Posted Tuesday, August 28, 2012 10:05 AM


    Old Hand

    Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

    Group: General Forum Members
    Last Login: Wednesday, March 19, 2014 9:43 AM
    Points: 316, Visits: 1,022


    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


    Post #1351073
    Posted Tuesday, August 28, 2012 10:09 AM


    SSCoach

    SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

    Group: General Forum Members
    Last Login: Monday, April 14, 2014 1:34 PM
    Points: 15,442, Visits: 9,588
    What you need to do is Full Outer Joins instead of Left Outer Joins. Join on Doc ID and Row Number.

    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;



    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
    Post #1351077
    « Prev Topic | Next Topic »

    Add to briefcase 1234»»»

    Permissions Expand / Collapse