Delete from 2 tables

Question

I have 2 tables in my DB

users...

ID    Username    Randomkey
1     Dionne      938493
2     Liam        902303
3     James       232039
4     Sarah       320923

interests...

ID    User_ID    Interest
1     3          Army
2     3          Boxing
3     3          Tennis
4     4          Make Up

In my interests table, 'User_ID' is equal to 'ID' from my users table.

I want to write a query that when triggered, deletes the row from my 'users' table and at the same time deletes all data relating to the user from the interests table.

I'm unsure how to do this and I've had no luck with Google.

If anybody could give me some advice or at least point me where to find this information it would be a great help.

In my head I'm thinking it would be something along the lines of:

DELETE FROM users, interests WHERE ID = '$userID' AND User_ID = '$userID'

Answer 1

DELETE users, interests 
FROM users 
LEFT JOIN interests ON users.ID = interests.User_ID
WHERE users.ID = <id>;

Of course, if the interests table was InnoDB & had a foreign key constraint to users with an ON DELETE CASCADE this would be done automatically when a user gets deleted.

Answer 2

If you're using InnoDB, then you can define a foreign key constraint for interests (User_ID) that references users.ID and has ON DELETE CASCADE. Then you just have to delete the row from users and all the rows in interests that reference that user will automatically be deleted.

If you're not using InnoDB, then Wrikken's solution works fine.