Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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]
share|improve this question

1 Answer

up vote 2 down vote accepted

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 !!!

share|improve this answer
 
GROUP_CONCAT does the magic! cheers –  AliBZ Aug 24 at 0:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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