MySQL join error

Question

I am trying to return the results of products in the table of products that have not been added by the current user and that the current user has not yet viewed, and then sort by products that have the smallest reviews showing those who do not have the first.

• tblUsers :

   UserID 1 2 3 

• tblProducts :

   ProductID UserID (created) NumberReviews --------- ---------------- ------------- 1 1 1 2 1 0 3 2 1 4 1 2 5 2 0 

• tblReviews :

   ReviewID UserID(reviewed) ProductID -------- ---------------- --------- 1 2 4 2 1 3 3 3 4 4 3 1 

So, for the current user from 2, I want to return

 Product ID ---------- 2 1 

I tried several different left associations, but to no avail. Any ideas?

Answer 1

 SELECT p.* , COUNT(r.ProductID) AS reviews FROM tblProducts AS p LEFT JOIN tblReviews AS r ON r.ProductID = p.ProductID WHERE UserID <> @currentUser AND NOT EXISTS ( SELECT * FROM tblReviews ru WHERE ru.ProductID = p.ProductID AND ru.UserID = @currentUser ) GROUP BY p.ProductID ORDER BY reviews ASC 

If you do not want to count, but use the NumberReviews field to order, this is simpler:

 SELECT p.* FROM tblProducts AS p WHERE UserID <> @currentUser AND NOT EXISTS ( SELECT * FROM tblReviews ru WHERE ru.ProductID = p.ProductID AND ru.UserID = @currentUser ) ORDER BY NumberReviews ASC 

Answer 2

Try:

 SELECT u.*, p.*, r.* FROM users u LEFT JOIN products p ON p.userId <> u.userID LEFT JOIN reviews r ON ( r.productId = p.ProductID AND r.UserID = u.userID) GROUP BY p.productID ORDER BY p.NumberReviews 

Answer 3

Something like these lines might work (SQL server syntax, not sure about mysql)

 Select productID from Users u -- all products not created by user inner join Products p on p.UserID != u.UserID -- that were reviewed by the user. NOT! left outer join Reviews r on p.ProductID = r.ProductID and r.UserID = u.UserID where r.ReviewID is null and User = CurrentUser 

Answer 4

If I haven't missed anything:

 SELECT p.ProductID FROM tblProducts p LEFT JOIN tblReviews r ON p.ProductID = r.ProductID AND r.UserID = @UserID WHERE p.UserID <> @UserID AND r.ReviewID IS NULL ORDER BY p.NumberReviews