MYSQL IN statement returning wrong data

Question

I'm trying to use the MYSQL statement IN to select only the data I need from my table. For example: I have a field that save categories by ID separated by comma (7,1,10,25,20). If I use this code:

SELECT * FROM cancelamento WHERE ( motivos_int_id IN ( 7 ) )

I get only the rows that start with the number 7. If the data from that row is 8,7,14, for example, it doesn't return any data. Even if I change the code to:

SELECT * FROM cancelamento WHERE ( motivos_int_id IN ( 7,8 ) )

Am I doing something wrong? I need to fetch all the data that has the number 7, for example, regardless of the order of the number 7.

@Strawberry Although I agree with you, that's not always possible. Sometimes there's just that Genius DataBase Admin™ who don't need no stinking join... — Ortiga, Nov 26 '13 at 17:49

score 1 · Accepted Answer · answered Nov 26 '13 at 17:48

1

you can use FIND_IN_SET

    SELECT * FROM cancelamento WHERE find_in_set(7 , motivos_int_id)

a little example here

answered Nov 26 '13 at 17:48

echo_Me

37,078
5
58
78

It worked: SELECT * FROM cancelamento WHERE FIND_IN_SET(8, motivos_int_id) AND FIND_IN_SET(7, motivos_int_id); – Foreba Nov 26 '13 at 18:15
I think this has the same caveat as using `LIKE`, that is, it won't use indexes. – Joachim Isaksson Nov 26 '13 at 18:21
1

@Foreba yes if you want add 8 then use AND ... and you will get what you want 7 and 8 . ok well it worked for you , good luck. – echo_Me Nov 26 '13 at 18:27

score 0 · Answer 2 · answered Nov 26 '13 at 17:46

First print your motivos_int_id separate , check your id'structure it could be like this one (12,3,4,) in last there is comma appended , so if this scenerio happend use rtrim() function in php and trim your right side comma and your query is correct .

select * from tbl_name where id IN(1,2,3)

MYSQL IN statement returning wrong data

2 Answers2