Conditional SQL Join based on Count, and Delete

Question

I'm trying to find all rows in table 'wpi9_pc_products' that have less than 3 entries in table 'wpi9_term_relationships', so that I can delete them from both.

Before shooting from the hip, I want to ensure I'm pulling the right rows for deletion, using this query:

SELECT * FROM `wpi9_pc_products` INNER JOIN wpi9_term_relationships ON 
wpi9_pc_products.id_product=wpi9_term_relationships.object_id GROUP BY 
wpi9_term_relationships.object_id HAVING COUNT(*) < 3

However, I'm stuck here trying to figure out the correct way to join the two tables conditionally based on the count.

My SQL is very, very rusty, so any help would be greatly appreciated!

Answer 1

If I am following correctly, you have a parent/child relationship between the products and the relationships table. I would recommend setting up a foreign key constraint on the child table, that references the parent table, like so:

alter table wpi9_term_relationships
    add constraint fk_product_id
    foreign key (object_id)
    references wpi9_pc_products(id_product)
    on delete cascade;

Then, you can simply delete the records from the parent table; the on delete cascade clause ensures that the children records will be automatically deleted:

delete p
from wpi9_pc_products p
where (select count(*) from wpi9_term_relationships r where r.object_id = p.id_product) < 3