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.

Valerio

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

Nowak

this did it, thank you, this join thing is really simple when you know what are you doing – Valerio

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'd make copies of the tables in question and test that the above works on the test tables before using this on your production tables.

Basically, the nested select will return all productRatings id's that the user who wrote the rating also made a comment on. Using NOT IN, it will delete all ratings that the user who made the rating didn't also comment.

As pala_ highlighted in the comments below, because this method uses nested sql, it will perform worse than a method that only uses joins on larger tables.

Clip

for large tables, this will perform worse than a version which uses only joins. – Nowak

@Nowak yea, I will add that to my answer. Your answer seems superior in that it does not use nested sql. – Clip

Answer 3

You should be able to just join the fields in the query and then check if the comment is empty, like so...

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

The IS NULL may need to be replaced with = '' depending on the database engine you use.

Remember to test on a testing instance of the DB first!

Caesalpiniaceous

I'm not sure you need to join on the user table as both comments and ratings tables have user ids. – Clip

I think this will fail because it is a join and you haven't specified which table to delete from. – Clip

It will fail for more reasons than that. join performs an inner join, which means it will only return rows that match, so the is null portion of the where clause is always false, so even IF it was syntactically correct, it would never delete anything. – Nowak

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) 

Missing

This does not accomplish what the OP has asked. It wants to delete a product rating where no comment exists from the user who created the product rating. Not keep it if ANY comment exists – Nowak

@Nowak Thank you! I was to fast. – Missing

this would likely fail in that WHERE id = with a nested select that is going to provide more than one result. with your use of IS NULL should likely be IN instead of = – Clip