Mysql issue with WHERE in clause

What is wrong with my “where in” clause in mysql?

My two tables SEATS and REGISTERS look like this

--    SEATS                          REGISTERS
| seat_id (int) |     | register_id (int)| seat_id (varchar) |
|===============|     |==================|===================|
|     102       |     |      3           |   102,103,104     |
|     103       |     |    234           |   546,547         |

The query to fetch the matching results is

SELECT * FROM Seats s, Registers r 
WHERE s.seat_id IN (r.seat_ids) 
GROUP BY s.seat_id

Can someone figure out what’s wrong ? Thanks,

Answer

IN requires the list to be a literal list, not a comma-delimited string. Use FIND_IN_SET for that:

WHERE FIND_IN_SET(s.seat_id, r.seat_ids)