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
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.
, GROUP_CONCAT(site_file_roles.role_id) AS roles
ON site_file_roles.id = site_files.id
WHERE site_files.id = 1
Thought for sure the concat_ws was the answer.