concate_ws() from join table

Databases
1

I have a site files table and a site_files_roles table that joins the files table with user roles.

I want to query to the files table for entries for a specific category and join that query with the roles that are assigned to that file entry. BUT, what I’m wanting is to get back unique rows with a column called ‘roles’ (doesn’t exist) with a comma delimited list.

I think the answer lies with using the concat_ws() function, but can’t get it to work. Here’s what I had:


SELECT *, CONCAT_WS(',',SELECT role_id FROM site_file_roles WHERE id = site_files.id) AS roles
FROM site_files
WHERE id = '1'

So in the end, i would get back rows for each file in the category and then at the end of each row a ‘roles’ column that would look like 1,4,6,7 (if it was associated with the role id’s 1,4,6,7).

Thanks for any help.

2 
SELECT site_files.*
     , GROUP_CONCAT(site_file_roles.role_id) AS roles
  FROM site_files
LEFT OUTER
  JOIN site_file_roles 
    ON site_file_roles.id = site_files.id
 WHERE site_files.id = 1
GROUP
    BY site_files.id
3

thanks.

Thought for sure the concat_ws was the answer. :slight_smile: