CODEWITHSUNDEEP
mysqlsql
Sanooj T
Sanooj T

Reputation: 1327

comma separated field to compare in mysql

I searched so much to find an answer but i can't.Here is my question
I have a table named main_table like this:

╔════╦════════════════╦
║ id ║    seat_id     ║
╠════╬════════════════╬
║  1 ║   274115,      ║
║  2 ║  274116,274117,║ 
║  3 ║ 274113,274114, ║
╚════╩════════════════╩

These seat_id's are primary key of another table named sub_table

╔═════════╦════════════════╦
║ seat_id ║    seat        ║
╠═════════╬════════════════╬
║  274115 ║     186        ║
║  274116 ║     187        ║ 
║  274117 ║     188        ║
║  274118 ║     159        ║
╚═════════╩════════════════╩

I want all the seat related to main_table's seat_id

╔════════════╗
║    seat    ║
╠════════════╣
║    186     ║
║    187     ║
║    188     ║
╚════════════╝

What i have tried so far is with sub query

select seat from sub_table where seat_id in(select seat_id from main_table)

That's not helping me

Upvotes: 2

Views: 1020

Answers (2)

shalini
shalini

Reputation: 1290

First of all u didnt normalize your table so u cant use joins and query u used "select seat_id from main_table" --> is not an array . Try to pass array 

select seat from sub_table where seat_id in($array)

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use FIND_IN_SET:

SELECT seat
FROM sub_table AS t1
WHERE EXISTS (SELECT 1
              FROM main_table AS t2
              WHERE FIND_IN_SET(t1.seat_id, t2.seat_id) <> 0)

However, I would suggest normalizing table main_table, as it is always a bad idea to store comma separated values in a single field like you do.

Demo here

Upvotes: 3

Related Questions