JulianJ JulianJ - 3 months ago 7
MySQL Question

How to show usernames using GROUP_CONCAT?

I am trying to figure out how to show usernames instead of userid's in a mysql query that users GROUP_CONCAT. The following query works but shows user id's. How can I show usernames instead?

The query

SELECT p.user_name, p.user_id, up.file, GROUP_CONCAT(c.userid)
FROM tbl_uploads up
LEFT JOIN tbl_users p ON up.user_id = p.user_id
LEFT JOIN tbl_collab c ON up.file = c.file
GROUP BY up.file


Results

user_name | user_id | file | GROUP_CONCAT(c.userid)

Peter 5 pic_1.jpg 2,5
Mary 6 pic_2.jpg 6
julian 2 pic_3.jpg (null)

Answer

Join the users table one more time, and get the usernames from there:

SELECT p.user_name, p.user_id, up.file, GROUP_CONCAT(cp.user_name)
FROM tbl_uploads up
LEFT JOIN tbl_users p ON up.user_id = p.user_id
LEFT JOIN tbl_collab c ON up.file = c.file
LEFT JOIN tbl_users cp ON cp.user_id = c.userid
GROUP BY up.file
Comments