mysql where in clause with select statement after in clause

Question

I have a varchar field which store values like 2,1 and I am using a query to select the data. The query is this one:

SELECT no
FROM c_head          
where id = 9

which give output 2,1

and after that, I use this query

SELECT id, item 
FROM c_item 
where type_head IN (SELECT no
FROM c_head          
where id = 9)       
ORDER BY item

and its not working its working like type_head IN (2) instead of type_head IN (2,1)

but when I simply use hard coded its working

 SELECT id, item 
    FROM c_item 
    where type_head IN (2,1)       
    ORDER BY item

but its not working with subquery why?

please help me

So you're saying that `no` is a *text* field that contains the *text* `2,1`? Is that correct? — Mr. Llama, Jun 30 '14 at 17:51
possible duplicate of [FIND\_IN\_SET() vs IN()](http://stackoverflow.com/questions/4155873/find-in-set-vs-in) — showdev, Jun 30 '14 at 17:57
The reason it isn't working is because `IN (number, number, ...)` is a list of number values where `IN (text_with_commas)` is comparing to a *single* text value, not a list of numbers. — Mr. Llama, Jun 30 '14 at 18:00
since the subselect returns a literal `2,1`, you're doing `WHERE foo IN ('2,1')`, and the 2,1 is treated as a monolithic value/string, not a CSV value. — Marc B, Jun 30 '14 at 18:21

score 0 · Answer 1 · answered Jun 30 '14 at 18:20

0

i found my answer

SELECT id, item 
FROM c_item 
where  FIND_IN_SET(type_head, (SELECT no
FROM c_head          
where id = {head}))       
ORDER BY item

thanks........

answered Jun 30 '14 at 18:20

S.J.

19
8

score 0 · Accepted Answer · answered Jun 30 '14 at 18:21

To store comma separated values in column is bad design you should look at Database Normalization and do normalize your structure by storing all related type_head in a junction table,but if you can't change structure so in mysql you can use FIND_IN_SET() and join your c_head table,it will select records if it find the value in provided set,IN() will not work for values in set or in comma separated list

SELECT i.id, i.item 
FROM c_item i
JOIN c_head h ON(FIND_IN_SET(i.type_head,h.no) > 0)
WHERE h.id = 9
ORDER BY i.item

mysql where in clause with select statement after in clause

2 Answers2

Linked