MySQL SELECT query to fetch products not created or reviewed by the current user, ordered by review count
06:16 07 Jul 2011

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?

mysql join