Mysql query to concat rows

Question

Mysql tables structure is

B1 //table 1

aid         name     
1            aaa
2            bbb
3            ccc
4            ddd

B2 //table 2

bid         aid
111          1
222          2
333          3
444          3
555          4

I want to display data like following

aid      bid             name
1        111              aaa
2        222              bbb
3        333,444          ccc
4        555              ddd

I tried to do but not getting result as expected. How to do this using subquery or joins?

Thank you.

possible duplicate of [Can I concatenate multiple MySQL rows into one field?](http://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field) — M Khalid Junaid, Jun 09 '14 at 12:07
I saw that but that was from single table. I want to do by using 2 tables — Mayuri, Jun 09 '14 at 12:09
Are you familiar with join if not first read joins and then try to map the dup solution on your query — M Khalid Junaid, Jun 09 '14 at 12:10

score 3 · Answer 1 · answered Jun 09 '14 at 12:20

3

This is what i have tried to describe in comments,you can use group-concat

Join approach

select t.aid,group_concat(t2.bid) bid
from t
join t2 using(aid)
group by t.aid

Demo

Single table approach

select t2.aid,group_concat(t2.bid) bid
from t2
group by t2.aid

Demo

Beware of that fact it has a default limit of characters to concat as per 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.The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet

answered Jun 09 '14 at 12:20

M Khalid Junaid

63,861
10
90
118

I don't think "single table approach" is addressing the issue OP had and it should be obious from the "join" example you have. It might also be worthwhile to consider `GROUP_CONCAT(DISTINCT t2.bid)` even though it isn't clear whether OP needs that. – vhu Jun 09 '14 at 12:22
@vhu I have posted answer for the [**previous version of question**](http://stackoverflow.com/revisions/24119890/1) if you see the edits of question and first sample dataset it doesn't have a name column in first table – M Khalid Junaid Jun 09 '14 at 14:36

Muhammad Raheel · Accepted Answer · 2014-06-09T12:25:43.250

1

You can use group_concat

SELECT
    B1.aid,
    GROUP_CONCAT(DISTINCT B2.bid) bids,
    B1.name
FROM B1
LEFT JOIN B2 ON B1.aid = B2.aid
GROUP BY B1.aid

Fiddle Demo

Output

| AID |    BIDS | NAME |
|-----|---------|------|
|   1 |     111 |  aaa |
|   2 |     222 |  bbb |
|   3 | 333,444 |  ccc |
|   4 |     555 |  ddd |

edited Jun 09 '14 at 12:25

answered Jun 09 '14 at 12:19

Muhammad Raheel

19,823
7
67
103

Mysql query to concat rows

2 Answers2

Join approach

Demo

Single table approach

Demo

Fiddle Demo