Hello,

The first query below is not returning my row? It should... I am at a loss. The stored value in a.genrePIDs is "131,79". If the value is 131, 2nd query below, then my row is returned. But no row is returned if I am looking for 79. If I add the string '79' between the (,'79')'s, 3rd query below, then my row is returned.

Any ideas?


SELECT a.trackID
FROM tracks a
WHERE 79 IN (a.genrePIDs) <- ROW IS NOT RETURNED


SELECT a.trackID
FROM tracks a
WHERE 131 IN (a.genrePIDs) <- ROW IS RETURNED


SELECT a.trackID
FROM tracks a
WHERE 79 IN (a.genrePIDs,'79') <- ROW IS RETURNED


Using: MySQL 4.1

Thanks

this is a great example of why they tell you never to store a comma-separated list of values in a single column

it breaks first normal form and makes joining on the column next to impossible

can you redesign the table properly?

jap looks like a refactoring is on schedule

you can do someting like this

make a assignment table
track_id, genre_id both smalest type you expect
mediumint 8 unsigned should do it (if no negative values are needed)

and later do a subselect
somthing like
WHERE X IN (SELECT genre_id FROM assignment_tabe b WHERE b.track_id = a.track_id )

i like the 4.1 version :0)