Mysql issue with WHERE in clause

Question

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,

Barmar · Accepted Answer · 2014-03-12 11:48:52Z

1

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)

answered Mar 12, 2014 at 11:48

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Diogo Mendonça Over a year ago

wow, this really works :O never heard of find_in_set before! Going to save it forever! hehe :) many thanks

Barmar Over a year ago

It's right there on the documentation page of string functions. dev.mysql.com/doc/refman/5.1/en/string-functions.html

rsakhale · Accepted Answer · 2014-03-12 11:51:35Z

0

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

answered Mar 12, 2014 at 11:51

rsakhale

1,03613 silver badges27 bronze badges

Collectives™ on Stack Overflow

Mysql issue with WHERE in clause

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related