Sanooj T Sanooj T - 7 months ago 26
SQL Question

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

Answer

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