MYSQL combine multiple rows into single row using join with another table

Question

I want to join a table with another table but i need multiple row values from second table.

Table #1:(orders)

userid  order_id

1412    1412_AD_150910
1413    1413_AD_150910

Table #2:(follow_up)

userid date    comments        called

1412   150910   test comments    1
1412   150911   comments1        0
1413   150912   test             1

Results will be like this:

userid  order_id         follow_up_details

1412    1412_AD_150910   [{150910,testcomments,1},{150911,comments1,0}]
1413    1413_AD_150910   [{150912,test,1}]

Basically i want to combine rows from second table using userid.

Thanks in advance.

Mauker · Accepted Answer · 2015-09-10 14:26:17Z

I see you want something like a JSON, right? Then you could use a combination of GROUP_CONCAT with CONCAT to achieve your goal.

Something like:

SELECT
  t1.userid,
  t2.order_id,
  CONCAT('[',GROUP_CONCAT(CONCAT('{',t2.date,',',t2.comments,',',t2.called,'}')),']') AS follow_up_details
FROM
  table1 AS t1
INNER JOIN table2 AS t2 ON t1.userid = t2.userid
GROUP BY
  t1.userid;

P.S.: This kind of string formatting is better done on your application (on your PHP code for example), as if those tables have many entries, your query will get very slow.

P.S.2: From the docs:

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024.

You can change that by running the following code before your SELECT.

SET group_concat_max_len = 4096;

Of course, set any value you like instead of 4096.

The syntax is:

SET [GLOBAL | SESSION] group_concat_max_len = val;

Abhik Chakraborty · Accepted Answer · 2015-09-10 13:27:25Z

2

These type string formatting should be done on the application level in general. However if there is a constraint that it needs to be done on DB level you can do as

select
o.userid,
o.order_id,
concat('[',group_concat(concat('{',f.date,',',f.comments,',',f.called,'}')),']') as follow_up_details
from orders o 
join follow_up f on f.userid = o.userid
group by o.userid

answered Sep 10, 2015 at 13:27

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Collectives™ on Stack Overflow

MYSQL combine multiple rows into single row using join with another table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related