mysql table join problem

Question

table: user

id     name
-------------
1      john
2      paul
3      mattew

table: nickname

id    user_id    nickname
--------------------------
1     1          frog
2     1          cow
3     1          bull
4     2          cat

Result I want:

 1     john     frog cow bull
 2     paul     cat
 3     mattew     

How can I get this result?

Answer 1

Something like this should work:

SELECT u.id, u.name, GROUP_CONCAT(n.nickname, ' ') AS nickname
FROM user u
LEFT JOIN nickname n ON u.id = n.user_id
GROUP BY u.id, u.name

Please Note: syntax for GROUP_CONCAT may not be perfect because I haven't used it in a while

Answer 2

Use mysql aggregate functions: Group concat will do the trick for you.

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat