0

Possible Duplicate:
MySQL query finding values in a comma separated string

I have a strange problem, that I hope you guys can answer for me. If i use g.questions (which is a commaseperated varchar list), this only outputs the first id. But if I manually types in "1,2,3,4" instead of g.questions, I get what I expect...

What's going on? :-)

SELECT q.id FROM `questions` q 
LEFT OUTER JOIN `game` g 
ON g.id = 1 
WHERE q.id IN (g.questions)
Community
  • 1
  • 1
curly_brackets
  • 5,491
  • 15
  • 58
  • 102
  • 1
    You cannot do it that way. `g.questions` is a single string from MySQL's point of view and cannot be substituted for an `IN()`. MySQL sees it as `WHERE q.id IN ('1,2,3,4')` – Michael Berkowski Jan 19 '13 at 02:55
  • @MichaelBerkowski But why does it return the first id then? The first id is not equal to the concatenated string – Chetter Hummin Jan 19 '13 at 02:57
  • How are you constructing the g.questions varchar list? Is it queried from a table in some manner? – Chetter Hummin Jan 19 '13 at 02:58
  • @ChetterHummin hard to say without seeing the data perhaps because of the left join on `g.id =1` – Michael Berkowski Jan 19 '13 at 02:59
  • @ChetterHummin If you are in a position to change this schema, I strongly recommend fixing this so the column is normalized out into another table rather than stored as a list. It will cause increasing headaches and always be basically immune to good indexing. – Michael Berkowski Jan 19 '13 at 03:02
  • Would you create tables for each of those cells? That would mean thousands of tables...? – curly_brackets Jan 19 '13 at 03:17
  • No, a table with 2 columns which are foreign keys to `q.id` and `g.question_id` (or whatever that column is called). For each `q.id`, you would have multiple rows in the table which link it to a question id. – Michael Berkowski Jan 19 '13 at 03:23

1 Answers1

0

This would accomplish what you are trying to do:

SELECT q.id FROM `questions` q 
LEFT OUTER JOIN `game` g 
ON g.id = 1 
WHERE concat(',',g.questions,',') like concat('%,',q.id,',%')

Adds a comma before and after your comma separated string to make sure each value is bound by commas then uses like to match ",1,2,3,4," with "%,2,%" or whatever your q.id is.

Victor Bruno
  • 1,033
  • 7
  • 12