What is wrong with my "where in" clause in mysql? My two tables SEATS and REGISTERS look like this The query to fetch the matching results is 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:

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         |

​x
 
--    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

 
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)

 
WHERE FIND_IN_SET(s.seat_id, r.seat_ids)​

Advertisement

Answer