CODEWITHSUNDEEP
phpmysqljoinmultiple-tables
dbr
dbr

Reputation: 1047

mysql select/delete using join over four tables

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.

Upvotes: 7

Views: 103

Answers (4)

Bob Nocraz
Bob Nocraz

Reputation: 446

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.

Upvotes: 2

smiggle
smiggle

Reputation: 1259

  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)

Upvotes: 0

James Grundner
James Grundner

Reputation: 1484

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!

Upvotes: 1

pala_
pala_

Reputation: 9010

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

Upvotes: 4

Related Questions