CODEWITHSUNDEEP
phpmysqlsql
Mostafa Talebi
Mostafa Talebi

Reputation: 9183

How to join a table to find the items which do not exist

my question may seem strange. But I have a question, how should I do a JOIN on a table for the following purpose:

I have a table called categories.

I have another pivot table called categories_products

Regularly I do a left join to get the categories of one product.

Now, after several month, there are some categories deleted from categories table. I had forgot to put a trigger at that time (that delete all related categories_products records of the deleted cat). I want to delete any record (which now is considered to be unused) from categories_products where its categories item is deleted (does not exist). What query should I write? Nothing comes to my mind. Thanks

Upvotes: 0

Views: 45

Answers (3)

Ajit Kumar
Ajit Kumar

Reputation: 345

DELETE categories_products where id not in (select * from (select c.id form categories_products cp JOIN categories c ON cp.categories_id =c.id)x);

Upvotes: 1

Hartmut Holzgraefe
Hartmut Holzgraefe

Reputation: 2765

This multi-table delete should be even faster as it doesn't require a subquery (which MySQL usually isn't good at):

DELETE categories_products
 FROM categories_products
 LEFT JOIN categories
 ON categories_products.categories_id = categories.id
 HAVING categories.id IS NULL;

Upvotes: 0

Multisync
Multisync

Reputation: 8797

delete from categories_products cp 
where not exists (select 1 from categories c where c.id = cp.category_id);

Or

delete from categories_products cp 
where cp.category_id not in (select distinct c.id from categories c);

But EXISTS is usually faster.

Upvotes: 3

Related Questions