[MySQL] selecting and concatenating strings - can it be done?

[Sic]

hey, i'm drawing a blank on this, was wondering if any experts can cast an eye over it.

i have a table that contains a bunch of data, and this data can appear in multiple categories. this relationship is stored in a column called categoryID as comma separated data (eg. 3,10,12,16).

using the previous example, 3 relates to a category name in a separate table, as do 10, 12 and 16 etc. my question is, can i pull all of the category names from the other table using the comma separated values in the categoryID column?

this was one that sounded pretty simple, but has gotten a bit silly, and now i'm just confused! i think i might have to search the comma separated values recursively but my MySQL isn't as good as it should be.

so far, i have this:

SELECT CONCAT_WS(',',blogCategory.category) FROM blogEntry LEFT JOIN blogCategory ON (blogEntry.categoryID LIKE blogCategory.categoryID)

it's only bringing back the first piece of data in the comma separated list, but obviously i'd like it to bring back everything it can...any chances?

many thanks

 

[Dj_Jestar]

Don't use delimeted data, it's bad practice. Create a new table for the would be delimeted data (a new record for each) and use a foreign key.

 

[Sic]

of course! i've done that now, but i'm still having trouble grabbing the data all at once

im doing select concat and grouping by blogID but it's only taking the first result still. i'm obviously doing something wrong with my concat, but i can't figure it out:

select concat(categoryID) as catID from blogEntryCategory WHERE blogID = 88

is returning the values individually in separate rows and:

select concat(categoryID) as catID from blogEntryCategory WHERE blogID = 88 GROUP BY blogID

is just returning the first. how can i recursively append these ids to the catID column (this is eventually going to be a nested select statement when it's working!)

thanks for your help

 

[Sic]

aha! GROUP_CONCAT is my friend!