I've got an issue trying to join tables. Here's an example.
ID CategoryID Title
1 ,1,2,3, Example Title
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?
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
The category_id can be omitted. I added it to the select to validate the comma delimited field.