Theres already a post here about deleting rows from some table using results from a different query. This is basically the same but more raw/basic.
So i have:
– a table ‘events’ with id_event,title, etc.
– a table ‘users’ with id_user, name, etc.
– a table ‘rel_users_events’, which is a relationship between ‘users’ and ‘events’, with id, id_user,id_event
Lets assume i want to remove a user (id_user=1) from the system and all his events.
We can do this:
DELETE FROM events
WHERE id_event IN
(SELECT id_event FROM rel_users_events WHERE id_user=’1′)
This first executes the subquery SELECT, getting a list of all the event ids that user 1 has. Then it uses that list to delete all those event ids from the table ‘events’.
NOTE: This problem could be solved using other methods, obviously, like procedures, programming, magic, etc. but i wanted to show a pure SQL approach.