deleting childless records

I have a users table and a transactions table. The transactions table references the id field in the users table for the sender and receiver like this:

SELECT t.id AS id, uf.username AS username_from, ut.username AS username_to, t.date_time AS transaction_date, t.amount AS amount FROM users AS uf, users AS ut, transactions AS t WHERE uf.id = t.user_id_from AND ut.id = t.user_id_to

Code (markup):

I've created my own user manager which allows me to delete users. It sets the status field in the users table to 'deleted' so that it won't show up in the list. I don't want to actually delete a user record unless it has no child records in the transactions table. Next I'm trying to create a statement that deletes all records from the users table where the status field is set to 'deleted' and for which there are no child elements in the transactions table either in the user_id_from or user_id_to fields...any idea how I do that in a single statement?

 

Something like:

DELETE FROM users
WHERE  status = 'deleted'
       AND ( id NOT IN (SELECT user_id_from
                        FROM   transactions)
             AND id NOT IN (SELECT user_id_to
                            FROM   transactions) );  

Code (markup):

Make sure to check the column names and do a backup before running this.

 

ok, thanks! thought I would going to have to get into a bunch of table joins & stuff

 

wow thanks share guyss