Joining Multiple Rows in a JOIN

Question

Table_One:

+---------+----------+
|  USER_ID|   name   |
+---------+----------+
|    2    |   Rick   |
+---------+----------+

Table_Two

+---------+----------+----------+
|  t2_ID  |  sport   |  USER_ID |
+---------+----------+----------+
|    32   | football |     2    |
+---------+----------+----------+
|    33   | baseball |     2    |
+---------+----------+----------+

The result I am hoping for is the following:

+---------+----------+--------------------+
|    2    |  Rick    | Football, Baseball |
+---------+----------+--------------------+

I don't understand how to combined multiple rows from an INNER JOIN into one column like this.

SELECT Table_One.* FROM Table_One LEFT JOIN Table_Two ON Table_Two.USER_ID = Table_One.USER_ID

I am getting results as separate rows:

+---------+----------+----------+
|    2    |  Rick    | football |
+---------+----------+----------+
|    2    |  Rick    | baseball |
+---------+----------+----------+

Thank you everyone who read this.

This goes against the principles of structured data tables, if you have to do it you can use `string_agg()`: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017 — user8834780, Oct 04 '18 at 14:10
@user8834780 That's why I asked for the version, it's available only on 2017+ — sagi, Oct 04 '18 at 14:11
good point, if it is before 2017- see @Yogesh Sharma's answer below — user8834780, Oct 04 '18 at 14:12
nice i didn't know you could do that with string_agg(), when that was not available, i used to pivot and concat values. — Alvaro Parra, Oct 04 '18 at 14:13
Sequal Pro is for MySQL. You've tagged this as SQL-Server. Please correct your post. — Brian, Oct 04 '18 at 14:13

score 2 · Answer 1 · answered Oct 04 '18 at 14:10

2

THIS ANSWERS THE QUESTION BASED ON TAG BEFORE ANY COMMENT/EDIT MADE.

You can use FOR XML PATH() clause :

SELECT to.*,
       STUFF( (SELECT DISTINCT ', ' +tt.sport   
               FROM Table_Two tt
               WHERE tt.USER_ID = to.USER_ID
               FOR XML PATH('')
              ), 1, 1, ''
           ) AS Sports
FROM Table_One to;

answered Oct 04 '18 at 14:10

Yogesh Sharma

49,870
5
26
52

I was mistaken it is MySQL 5.6.27 Should this still work? I', getting an error near "XML_PATH – RON2015 Oct 04 '18 at 14:21
@RON2015. . . Then look @ `GROUP_CONCAT()` in MySQL. – Yogesh Sharma Oct 04 '18 at 14:22
Thank you for the help, I think I got it! – RON2015 Oct 04 '18 at 14:29

Joining Multiple Rows in a JOIN

1 Answers1