CODEWITHSUNDEEP
mysqlsql
Diogo Mendonça
Diogo Mendonça

Reputation: 923

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,

Upvotes: 1

Views: 101

Answers (2)

rsakhale
rsakhale

Reputation: 1036

Firstly change the seat_ids to int, else maintain a separate table where the linkage of seats and registers are maintained.

Join like below can help club results of 2 tables

SELECT * FROM Seats s
JOIN Registers r ON s.seat_id = r.seat_ids

But this will require same data type of Seats seat_id & Registers seat_ids

Else what @Barmar said can be used

Upvotes: 0

Barmar
Barmar

Reputation: 782584

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)

Upvotes: 1

Related Questions