8

I have these 2 tables:

table1:

id | name
---------
1  | john
2  | jack

table2:

id | profile_id | institution
-----------------------------
1  | 1          | SFU
2  | 1          | UBC
3  | 2          | BU
4  | 2          | USC
5  | 2          | SFU

If I want to get all the information about a user using his userid, I can simply join them using this:

select a.id, a.name, b.institution from table1 a, table2 b
where a.id = USER_ID and a.id = b.profile_id

which for USER_ID = 1 returns:

id | name | institution
-----------------------
1  | john | SFU
1  | john | UBC

What I need is actually 1 unique row instead of multiple rows. Is it in any way possible to get something like this? (I haven't seen something to do it but am not sure)

id | name | institution
-----------------------
1  | john | [SFU, UBC]

1 Answer 1

8

You can use the GROUP_CONCAT function

SELECT a.id, a.name, CONCAT('[',GROUP_CONCAT(b.institution),']') institution
FROM table1 a INNER JOIN table2 b
ON a.id = b.profile_id
WHERE a.id = USER_ID
GROUP BY a.id, a.name;

or with your original query

select a.id, a.name, CONCAT('[',GROUP_CONCAT(b.institution),']') institution
from table1 a, table2 b where a.id = USER_ID and a.id = b.profile_id
group by a.id, a.name;

Give it a Try !!!

2
  • GROUP_CONCAT does the magic! cheers
    – AliBZ
    Aug 24, 2013 at 0:58
  • @RolandoMySQLDBA "...and here my watch ends" :P, I was looking for this since a long time. Thanks Nov 6, 2014 at 10:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.