[SOLVED] match then delete records

[bulgin]

I want to find all those records in table 'b' that have NO corresponding match on userid in table 'a', and then delete those non-matching records from table 'b'. Userid is existing in both table 'a' and 'b'

 

I know this is basic but my mind is drawing a blank right now and I could use some help.

 

Thanks.

 

 

[ldougherty]

http://dev.mysql.com/doc/refman/5.0/en/delete.html

 

multiple-table DELETE statements can use the same types of join allowed in SELECT statements, such as LEFT JOIN. For example, to delete rows that exist in t1 that have no match in t2, use a LEFT JOIN:

 

DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

 

[bulgin]

Thank you!  That works great!  

 

Now one last quick question and I can sleep: what if I have more than one table that needs to have rows deleted in the same scenario.  Following up on your post, say, for example, to delete rows that exist in t1 that have no match in t2 OR t3

 

that looks like a tricky one.  I can of course just use the same script and change the table.column names, but I think there must be a smoother way to do it.

[fenway]

The query determine what "matches" -- with your LEFT JOINs and such -- and you choose with matching table rows get deleted.

[bulgin]

Okay, thank you for your help.