SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru downtroden's Avatar
    Join Date
    Dec 2004
    Location
    illinois
    Posts
    976
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    concate_ws() from join table

    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:
    Code MySQL:
    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.
    your brain reacts in the same way whether you are
    looking at something or thinking about it...

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru downtroden's Avatar
    Join Date
    Dec 2004
    Location
    illinois
    Posts
    976
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks.

    Thought for sure the concat_ws was the answer.
    your brain reacts in the same way whether you are
    looking at something or thinking about it...


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •