Mysql select / delete using four table join

Question

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

users [id]

products [id]

productRatings [id,value,user,product]

comments [id,product,user]

I would like to select / and end up removing productRatings where there is no corresponding single user comment for this product. That is, if the user has a product rating but does not comment, this rating should be deleted.

I believe that I could achieve this using two queries: first

 SELECT user, product FROM productRatings 

and then for each line:

  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 problem through JOIN and learn more by example, rather than scrolling through JOIN tutorials.

Answer 1

You only need a table of products and comments - 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 is a demo here: http://sqlfiddle.com/#!9/89575/1

Answer 2

 DELETE FROM `productRatings` WHERE productRatings.id NOT IN ( SELECT productRatings.id FROM `productRatings` JOIN `comments` ON productRatings.user = comments.user AND productRatings.product = comments.product ) 

I would make copies of the tables in question and verify that the above works in test tables before using this in your production tables.

In principle, the nested select will return the entire productRatings identifier, which was added by the user who wrote the rating. Using NOT IN , it will remove all ratings that the rating user has not commented on.

As shown in the comments below, since this method uses nested sql, it will work worse than a method that uses only joins in large tables.

Answer 3

You should be able to simply join the fields in the request and then check if the comment is empty, for example ...

 DELETE FROM `productRatings` JOIN `comments` ON (`productRatings`.`product` = `comments`.`product`) WHERE `comments`.`comment_field` IS NULL AND `productRatings`.`user_id` = ?; 

It is possible that NULL IS should be replaced with = '' depending on the database engine you are using.

Remember to test the database test instance first!

Answer 4

  DELETE FROM productRatings WHERE id IN (SELECT pr.id FROM productRatings pr LEFT JOIN comments c on c.product = pr.product WHERE c.user = pr.user AND pr.comment IS NULL)