CODEWITHSUNDEEP
mysqljoinsql-delete
Udders
Udders

Reputation: 6986

Delete rows via a join and a WHERE condition that uses a different table

I have 3 tables in a mysql database, I am wanting to delete from 2 of them via a JOIN based on the condition that a 3rd table doesn't have any ID's remaining that pertain to another record.

So far I have, 

DELETE t1, t2
FROM t1
INNER JOIN t2 ON t1.qs_id = t2.qst_qs_id

What I can't work out is how to check the records of t3 to make a where clause work. The scenario I am trying to make work is as follows,

delete from t1 and t2, for any qs_ids that don't have any rows remaining in t3 table t3 has a column called qsa_qs_id, so if there are no rows matching the ids from the JOIN I can run the delete, but I cannot figure out how to put that into my WHERE.

PSEUDO would be,

DELETE from t1, t2, all rows that have matching qs_id columns BUT only do it if t3 doesn't have any rows with those qs_id remaining.

Upvotes: 0

Views: 25

Answers (1)

Lokesh Balaji
Lokesh Balaji

Reputation: 46

DELETE t1, t2 
FROM t1
INNER JOIN t2 ON t1.qs_id = t2.qst_qs_id 
WHERE t1.qs_id NOT IN (SELECT t3.qs_id FROM t3);

Upvotes: 1

Related Questions