select * where not in (other table values)-CodePudding

I have a reserves table like this:

id	name	room_ids
1	A	1,2,3
2	B	2,3,4
3	B	2,4,5

And rooms table like this:

id	room name	size
1	room a	1
2	room b	2
3	room c	1
4	room e	3
5	room f	1
6	room g	2
7	room h	2

Is it possible to get rooms with ids not in column room_ids in the reserves table with a single query? Something like: Select * from rooms where id not in (select room_ids from reserves).

CodePudding user response：

Yes, it's possible:

select rooms.*
from rooms
left join reserves
on concat(',', room_ids, ',') like concat('%,', rooms.id, ',%')
where reserves.id is null

Explanation:

left join yields at least one pair for each record of rooms
if rooms does not have a valid pair, then the reserves values will be nulls
so, in the where clause we check for null being the value of some not nullable reserves field
which ensures that we exclude any rooms that have any existing reservations

EDIT

If the problem is performance, then it would make sense to refactor the schema, create a new table called room_reservation(room_id, reservation_id), move the room_ids property's content out from reservations into this new table and perform a left join. Your main problem is that the current schema's plan has some flaws that you need to fix if you do not want to get into problems intermittently.