Menu
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.
493
MySQL DELETE JOIN with LEFT JOIN We can also use the LEFT JOIN clause in the DELETE statement to delete rows in a table (left table) that does not have matching rows in another table (right table). Note that we only put T1 table after the DELETE keyword, not both T1 and T2 tables like we did with the INNER JOIN clause.
The Inner Join query can be used with Delete query for removing rows from one table and the matching rows from the other table that fulfill the specified condition.
The following are the basic syntax of Delete Join with its parameters. The different parameters used in the syntax are: DELETE t1: It is used to delete the required table from the database. Here, you may choose from the first table's instance t1 and the second table's instance t2.
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
110
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
