Join table with comma delimited field

Databases
1

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

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

:smiley:

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

3

The category_id can be omitted. I added it to the select to validate the comma delimited field.