One-to-Many SQL SELECT into single row

Question

I have data in two tables.

The first table has a Primary Key called PKID

PKID  DATA
0    myData0
1    myData1
2    myData2

The second table has the PKID column from table 1 as a foreign key

PKID_FROM_TABLE_1  U_DATA
       0          unique0
       0          unique1        
       0          unique2
       1          unique3
       1          unique4
       1          unique5
       2          unique6
       2          unique7
       2          unique8

The basic SELECT statement I am making now is

SELECT a.PKID, a.DATA, b.U_DATA
FROM table1 as a
INNER JOIN table2 as b
ON a.PKID = b.PKID_FROM_TABLE_1

This produces a table like this:

PKID   DATA     U_DATA
 0   myData0    unique0
 0   myData0    unique1
 0   myData0    unique2
 1   myData1    unique3
 1   myData1    unique4
 1   myData1    unique5
 2   myData2    unique6
 2   myData2    unique7
 2   myData2    unique8

What I would like is the following table:

PKID   DATA    U_DATA1    U_DATA2    U_DATA3
 0     myData0 unique0    unidque1   unique2
 1     myData1 unique3    unidque4   unique5
 2     myData2 unique6    unidque7   unique8

If it helps, each PKID will have exactly 3 entries in table2.

Is something like this possible in MySQL?

See http://stackoverflow.com/questions/7674786/mysql-pivot-table — NotMe, Jul 11 '12 at 21:00
possible duplicate of [Join two tables (with a 1-M relationship) where the second table needs to be 'flattened' into one row](http://stackoverflow.com/questions/5826455/join-two-tables-with-a-1-m-relationship-where-the-second-table-needs-to-be-fl) — Randy, Jul 11 '12 at 21:05

spencer7593 · Accepted Answer · 2014-01-16T16:10:23.527

This is one way to get the result.

This approach uses correlated subqueries. Each subquery uses an ORDER BY clause to sort the related rows from table2, and uses the LIMIT clause to retrieve the 1st, 2nd and 3rd rows.

SELECT a.PKID
     , a.DATA
     , (SELECT b1.U_DATA FROM table2 b1
         WHERE b1.PKID_FROM_TABLE_1 = a.PKID 
         ORDER BY b1.U_DATA LIMIT 0,1
       ) AS U_DATA1
     , (SELECT b2.U_DATA FROM table2 b2
         WHERE b2.PKID_FROM_TABLE_1 = a.PKID 
         ORDER BY b2.U_DATA LIMIT 1,1
       ) AS U_DATA2
     , (SELECT b3.U_DATA FROM table2 b3
         WHERE b3.PKID_FROM_TABLE_1 = a.PKID 
         ORDER BY b3.U_DATA LIMIT 2,1
       ) AS U_DATA3
  FROM table1 a
 ORDER BY a.PKID

FOLLOWUP

@gliese581g points out that there may be performance issues with this approach, with a large number of rows returned by the outer query, since each subquery in the SELECT list gets executed for each row returned in the outer query.

It should go without saying that this approach cries out for an index:

ON table2 (PKID_FROM_TABLE_1, U_DATA)

-or, at a minimum-

ON table2 (PKID_FROM_TABLE_1)

It's likely the latter index already exists, if there's a foreign key defined. The former index would allow the query to be satisfied entirely from the index pages ("Using index"), without the need for a sort operation ("Using filesort").

@glies581g is quite right to point out that performance of this approach can be problematic on "large" sets.

Is it possible to do this for a dynamic number of U_DATA... For instance, my basic (high level) thought is something like this... b(x).U_data LIMIT x,1 — Alex Naspo, Nov 05 '12 at 20:59
Wouldn't it be too slow if there is a large number of records? — gliese581g, Jan 16 '14 at 04:58
@gliese581g: whether or not this query is "too slow" really depends on the requirements. You are right to point out that a "large number" of rows in table1 will mean that the subqueries in the select list will be executed a "large number" of times (once for each row returned from table1). The query I provided isn't the only approach, and it's not necessarily the most efficient. But whether the performance of this query is measured as "too slow", that really depends on the performance requirements. too — spencer7593, Jan 16 '14 at 15:50

score 21 · Answer 2 · answered Jul 19 '16 at 20:01

21

Depending on your release of MySQL, you can look into GROUP_CONCAT

answered Jul 19 '16 at 20:01

sdfor

6,324
13
51
61

9

This would actually be the cleanest solution as it does not require you to know how many list members you will have on the "many" side. Usage: `SELECT a.PKID ,GROUP_CONCAT(b.U_DATA) FROM a join b on a.PKID_FROM_TABLE_1 = a.PKID GROUP BY a.PKID;` – OlgaMaciaszek Jul 04 '17 at 10:02
SELECT requests.id,GROUP_CONCAT(membership_types.name_af) as needs FROM requests join request_membership_types_pivot on request_membership_types_pivot.request_id = requests.id join membership_types on request_membership_types_pivot.membership_type_id = membership_types.id GROUP BY requests.id; – Thomas Aug 02 '20 at 11:51

One-to-Many SQL SELECT into single row

2 Answers2

Linked