mysql select/delete using join over four tables

Question

I have four tables (in [] are columns):

users [id]

products [id]

productRatings [id,value,user,product]

comments [id,product,user]

I would like to select/and ultimately delete productRatings where there are no associated comment by the same user for that product. That is, if user has rated product but did not comment, that rating should be deleted.

I believe I could achieve that by using two queries, first:

SELECT user, product FROM productRatings

and then for each row:

    SELECT COUNT(*) FROM comments WHERE product=productRatings.product AND user=productRatings.user

and then something like

    if $queryAbove==0 : DELETE FROM productRatings WHERE id=productRatings.id

I would like to solve this via JOIN and learn more by example rather than dig through JOIN tutorials.

Answer 1

You only need the productratings and comments table - the following works:

delete pr
from productratings pr
left join comments c
  on pr.userid = c.userid
  and pr.productid = c.productid
where c.productid is null

And there's a demo here: http://sqlfiddle.com/#!9/89575/1