MySQL join issue

Question

I am trying to return the results of the products in the product table that were not added by the current user and that the current user has not already reviewed, then sort by the products that have had the least reviews showing those with none 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 a current user of 2 I want to return

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

I have tried a number of different left joins but to no avail. Any ideas?

Answer 1

If I didn't miss 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

Answer 2

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 don't want to count but use the field NumberReviews for ordering, it's 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 3

Something along 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

Try with:

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