Select and Delete with a single query

Question

I have 2 tables:

• users (user_id, user_connected)
• rooms (room_id, room_initiating_user_id, room_target_user_id)

I would like to delete all the "rooms" having both the initiating user and the target_user set to "user_connected=0"

I have two problems here:

1. How to target these users? Obviously this query won't work:

   SELECT room_id
   FROM rooms,users
   WHERE
   ( 
    (room_target_user_id=user_id) AND (user_connected=0)
   )                   
   AND
   (                
    (room_initiating_user_id=user_id) AND (user_connected=0)
   )
   

2. I would like, with the same query if possible, to delete these rooms (no problem if I use a second query but it means that this query would be triggered for each result, which is a lot. Isn't it possible to delete these rooms at once?

Solution

delete from rooms
where room_initiating_user_id in (select user_id from users where user_connected = 0)
  and room_target_user_id in (select user_id from users where user_connected = 0)