How delete records from table A and B, if B does not contain specific value

I am trying to compose SQL query for deleting data from two tables – A, B. They are connected with a key and only I need to check, if in B is specific value reservation in one column.

Long story short, I have table with rooms and beds and I need to check, if there is any reservation in room. If not, I need to delete room itself and all of its beds.

I’ve tried two queries, but none of them gives me expected results. Table B is relatively large, so I’m trying to make it as fastest as possible, in one query.

-- gives me always 0 records
DELETE
  a, b
FROM
  rooms a, beds b
WHERE
  a.id = b.id_room AND
NOT EXISTS
  (SELECT 1 FROM beds WHERE id_room = '45' AND state != 'reservation')


-- deletes all records, but rows where state = 'reservation'
DELETE
  a, b
FROM 
  rooms a, beds b
WHERE
  a.id = b.id_room AND id_room = '45' AND state != 'reservation'

Example dataset:

-- rooms
-------------
| id | Name |
-------------
| 1  | 601  |
| 2  | 602  |
| 3  | 603  |

-- beds
-----------------------------|
| id | id_room | state       |
-----------------------------|
| 1  | 1       | free        |
| 2  | 1       | free        |
| 3  | 1       | reservation |
| 4  | 2       | free        |
| 5  | 2       | free        |
| 6  | 3       | reservation |

Expected results:

Rooms 1 and 3 have value reservation in column state in table beds, so it can not be deleted. Room 2 has no reservation, so it can be deleted and it should be deleted including all its beds – so the query should delete record 2 from table rooms and records 4 and 5 from table beds.

I always want to delete only one room (with its beds), so I need to include condition about room ID to calculate with.

Answer

This query:

select id_room
from (
  select id_room, state from beds
  union
  select ?, ''
) t 
where id_room = ?
group by id_room
having sum(state = 'reservation') = 0

returns the id of the room that should be deleted, either because there is no 'reservation' for any of its beds or it has no beds.
Replace both ? with the id of the room that you search for.

Join it to the tables in the DELETE statement:

delete r, b
from rooms r 
inner join (
  select id_room
  from (
    select id_room, state from beds
    union
    select ?, ''
  ) t 
  where id_room = ?
  group by id_room
  having sum(state = 'reservation') = 0
) t on t.id_room = r.id
left join beds b
on b.id_room = t.id_room

See the demo.