SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    May 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Join table with comma delimited field

    I've got an issue trying to join tables. Here's an example.
    Main Table
    ID CategoryID Title
    1 ,1,2,3, Example Title

    Category Table
    ID CategoryName
    1 Ohio
    2 Texas
    3 Arizona


    I need the results to look like so....
    Main.ID Category Title
    1 Ohio,Texas,Arizona Example Title


    I've tried the query below and sometimes it looks correct and other times it doesn't. Even when the categories are the same in the main table.

    select a.id, group_concat(category_name) category, title
    from tableA a
    JOIN category c ON FIND_IN_SET(c.id, a.category_id) <> 0
    group by category_id, a.id, title
    order by a.id


    Is there anyway of parsing this to always get the results in the correct format?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, there is a way to make it always produce the correct result -- run the correct sql, and/or create the correct data values in the tables



    why are you grouping by a.id ~and~ category_id? if you do that, you get one row per combination, and the category will be unique, which pretty much assures that the GROUP_CONCAT is only gonna concat one category
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    May 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The category_id can be omitted. I added it to the select to validate the comma delimited field.


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
  •