SQL Select multiple rows into one displayed row

Question

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.

@tony Not necessarily. It could have one or two. If it has more it's a rare case, but possible. I do have conditions that could limit it to two. "WHERE DataD1 = 4" and "WHERE DataD1=22" — steventnorris
– steventnorris, Commented Jun 26, 2012 at 18:09
at best, you'll wind up with rows with some columns not populated, where there's only 1 join. that fine? Then just do 2 left joins to the second table and return the results. — Sean
– Sean, Commented Jun 26, 2012 at 18:13
You columns and and sample data don't match, and your where condition (in comment) will return zero records for the sample data. That combined with 500+ characters without a line break is very difficult to read and will present a barrier to people who want to help you. — Conrad Frix
– Conrad Frix, Commented Jun 26, 2012 at 18:28

Sean · Accepted Answer · 2012-06-26 19:14:59Z

4

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).

edited Jun 26, 2012 at 19:14

answered Jun 26, 2012 at 18:21

Sean

15.2k17 gold badges83 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

steventnorris Over a year ago

I tried your answer (posted attempt above) but no records were pulled. Maybe is one of the assumption? What information might help you understand my structure better?

steventnorris Over a year ago

Looked back over your answer, and actually this is where I got a lot of my direction from towards the final solutions. Apologies if I didn't give credit where credit was due.

steventnorris · Accepted Answer · 2012-06-26 19:09:49Z

2

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

answered Jun 26, 2012 at 19:09

steventnorris

5,93623 gold badges99 silver badges186 bronze badges

4 Comments

Sean Over a year ago

You really should have provided more information on what SData1 was storing, and how that would affect the selecting of rows.

whytheq Over a year ago

where has that 22 suddenly popped up from ?!

steventnorris Over a year ago

@whytheq The 22 was an answer to a question Tony asked in the comments of the OP.

steventnorris Over a year ago

@SeanW I gave a depiction of what I was attempting so as to get direction. The direction I was given, and the questions I was asked led me to this answer. The information as to what SData1 was holding was asked and answered in the comments of the OP. If you had asked, I would have gladly given you more information. Thanks for looking things over.

Collectives™ on Stack Overflow

SQL Select multiple rows into one displayed row

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related