CODEWITHSUNDEEP
sqlsql-servert-sqlrow
steventnorris
steventnorris

Reputation: 5896

SQL Select multiple rows into one displayed row

My situation is I have two tables. I want to join them together and have duplicate records appear on the same line. Mock table structures given below

MainTbl Cols: MKey1,MKey2,MData1,MData2,MData3

SuppTbl Cols: SPrimaryKey,SKey1,SKey2,SData1,SData2

I want to LEFT JOIN MainTbl to SuppTbl. However, SuppTbl contains duplicates of SKey1 and SKey2 combo key.

The results I want are below, Where "-#" indicates the duplication number.

MKey1,MKey2,MData1,MData2,MData3,SData1-1,SData2-1,SData1-2,SData2-2

In essence, all fields from the join should be contain on one row based one Key1 and Key2.

ATTEMPTED ANSWER BY SEAN W

SELECT
    MainTbl.MKey1,
     MainTbl.MKey2,
    tcd.SData1 AS SData11,  
    tcd.SData2 AS SData22,
    tcr.SData1 AS SData12,  
    tcr.SData2 AS SData22
FROM MainTbl
LEFT JOIN SuppTbl tcd
ON MainTbl.MKey1=tcd.SKey1 AND MainTbl.MKey2=tcd.SKey2
LEFT JOIN SuppTbl tcr
ON MainTbl.MKey1=tcr.SKey1 AND MainTbl.MKey2=tcr.SKey2
WHERE tcd.SData1 < tcr.SData1

RESULT No Success. Did not pull any records.

Upvotes: 0

Views: 10372

Answers (2)

Sean
Sean

Reputation: 15182

Revised (comments after):

CREATE TABLE MainTbl (MKey1 int,MKey2 int,MData1 varchar(10),MData2 varchar(10),MData3 varchar(10))
CREATE TABLE SuppTbl (SPrimaryKey int,SKey1 int,SKey2 int,SData1 varchar(10),SData2 varchar(10))

INSERT INTO MainTbl VALUES (1, 1, '1MData1', '1MData2', '1MData3')
INSERT INTO SuppTbl VALUES (1, 1, 1, '1SData1-1', '1SData2-1')
INSERT INTO SuppTbl VALUES (2, 1, 1, '1SData1-2', '1SData2-2')

INSERT INTO MainTbl VALUES (1, 2, '2MData1', '2MData2', '2MData3')
INSERT INTO SuppTbl VALUES (3, 1, 2, '2SData1-1', '2SData2-1')

SELECT
    MainTbl.MKey1,
    MainTbl.MKey2,
    tcd.SData1 AS SData11,  
    tcd.SData2 AS SData22,
    tcr.SData1 AS SData12,  
    tcr.SData2 AS SData22
FROM MainTbl
INNER JOIN SuppTbl tcd
ON MainTbl.MKey1=tcd.SKey1 AND MainTbl.MKey2=tcd.SKey2
LEFT JOIN SuppTbl tcr
ON MainTbl.MKey1=tcr.SKey1 AND MainTbl.MKey2=tcr.SKey2
AND tcd.SPrimaryKey < tcr.SPrimaryKey

Now this won't work 100% for those instances where you have 2 rows in SuppTbl: it will give two result rows - one will be fine and the other you will want to exclude. To exclude it, you have to provide some more information on how to identify those instances where it will have >1 SuppTbl row. You mentioned above in the comments "WHERE Data1 = 4". So that would need to be part of a WHERE clause. It would be something like:

 WHERE tcd.SData1 = 4

This might then EXCLUDE the single SuppTbl row. So you need to provide information on how to NOT have that row filtered out. Maybe:

 WHERE tcd.SData1 IN (4, 22)

(This won't work with the data in the tables, above).

Upvotes: 4

steventnorris
steventnorris

Reputation: 5896

Found an answer. I've trimmed it down a bit for simplicity's sake, but it works great so long as there are WHERE conditions that can be applied, as there are in my case.

SELECT 
    MainTbl.MKey1,
    MainTbl.MKey2,
    tcd.stat AS SData11,
    tcr.stat AS SData12
FROM MainTbl
LEFT JOIN(
    SELECT * FROM SuppTbl WHERE SData1 <> 22
) tcd
ON MainTbl.MKey1=tcd.SKey1 AND MainTbl.MKey2=tcd.SKey2
LEFT JOIN(
    SELECT * FROM SuppTbl WHERE SData1 = 22
) tcr
ON MainTbl.MKey1=tcr.SKey1 AND MainTbl.MKey2=tcr.SKey2

Upvotes: 2

Related Questions