Combine multiple rows from other table into 1 column in mysql

Question

I have two tables

table1

|---------------------|------------------|------------------|
|      student_id     |   studentname    |     parentid     |
|---------------------|------------------|------------------|
|          1          |       Kobe       |        1         |
|---------------------|------------------|------------------|
|---------------------|------------------|------------------|
|          2          |      Lebron      |        1         |
|---------------------|------------------|------------------|


table2
|---------------------|------------------|
|       parentid      |    parentname    |   
|---------------------|------------------|
|          1          |      Jordan      |
|---------------------|------------------|

I want to select an output like this

result
|---------------------|------------------|------------------|---------------- 
--
|       parentid      |    parentname    |    childrenid    |  childrenname
|---------------------|------------------|------------------|------------------
|          1          |      Jordan      |     1,2          |  Kobe,Jordan
|---------------------|------------------|------------------|------------------

is this possible to happen? thanks in advance I already use this query but the rows is duplicating

select * from table2 left join table1 ON table1.parentid = table2.parentid

score 0 · Answer 1 · answered Aug 05 '18 at 05:11

0

No it is not possible, you have to use left join which will result in two rows one for Kobe and other for Lebron.

answered Aug 05 '18 at 05:11

Roshaan Farrukh

399
3
10

Zaynul Abadin Tuhin · Accepted Answer · 2018-08-05T05:26:02.490

0

use GROUP_CONCAT funtion in mysql

select parentname,
GROUP_CONCAT(student_id SEPARATOR ',') AS childrenid,
GROUP_CONCAT(studentname SEPARATOR ',') childrenname  
from table2 p left join table1 s on
p.parentid=s.parentid
group by parentname

parentname  childrenid  childrenname
Jordan       1,2        Kobe,Lebron

http://www.sqlfiddle.com/#!9/4d9127/2

edited Aug 05 '18 at 05:26

answered Aug 05 '18 at 05:12

Zaynul Abadin Tuhin

31,407
5
33
63

if it helps then please accept my answer as SO appreciation – Zaynul Abadin Tuhin Aug 05 '18 at 05:37
btw sir how do I set the length of group_concat? – Cesar Wencester Aug 05 '18 at 05:44
@CesarWencester why do you need length if need then you can by usig length funtion – Zaynul Abadin Tuhin Aug 05 '18 at 05:45
because I will concat the fullname of each student and when I try the fullname of student is incomplete in the column childrenname – Cesar Wencester Aug 05 '18 at 05:51
actually it depends how long the name would be based on your idea you can set that – Zaynul Abadin Tuhin Aug 05 '18 at 05:52

Combine multiple rows from other table into 1 column in mysql

2 Answers2