February 8, 2006 at 2:22 pm
I have 3 tables I am working with, Products, Users, and Votes. Basically it allows a user to vote on a product and their score is added to the Votes table along with the Product ID and the User ID. I want to then display products sorted by the user's vote. One problem is the fact that the Vote table may not contain a given Product if the User has not voted on it.
So here is the query I have now:
SELECT PRODUCTS.PRODUCT_ID, PRODUCTS.P_NAME, VOTES.V_SCORE
FROM [PRODUCTS]
LEFT JOIN [VOTES] ON VOTES.PRODUCT_ID = PRODUCTS.PRODUCT_ID
WHERE PRODUCTS.STATUS_ID = 1 AND VOTES.USER_ID = 3
ORDER BY VOTES.V_SCORE ASC
This is working EXCEPT for the fact that it does not return the Products that have not been voted on. I was assuming that using LEFT JOIN would also return Products that did not appear in the Votes table but it is not working as I expected.
Anyone have any ideas?
February 8, 2006 at 2:48 pm
Hello Jim,
Can you post some sample data?
Also would you execute your query and check by removing the "AND" condition in the "Where" clause.
Thanks and have a nice day!!!
Lucky
February 8, 2006 at 2:52 pm
If you have a table that is left-joined, and you then apply a WHERE to that table, you force it back to an INNER JOIN. Try this:
SELECT PRODUCTS.PRODUCT_ID, PRODUCTS.P_NAME, VOTES.V_SCORE
FROM [PRODUCTS]
LEFT JOIN [VOTES]
ON VOTES.PRODUCT_ID = PRODUCTS.PRODUCT_ID AND
VOTES.USER_ID = 3
WHERE PRODUCTS.STATUS_ID = 1
ORDER BY ISNULL(VOTES.V_SCORE, 0) ASC
February 8, 2006 at 3:07 pm
Okay that is awesome and I totally understand why now and I actually figured that might have been problem... just wasn't sure how to fix it.
Now I have one more question. What if I want to sort by the maximum number of votes that a Product has been given regardless of User. Basically a SUM of the V_SCORE field. Is this possible?
Thanks again!
February 9, 2006 at 12:56 am
Yes, but it will require a subquery to do the calculation
SELECT PRODUCTS.PRODUCT_ID, PRODUCTS.P_NAME, VOTES.V_SCORE
FROM [PRODUCTS]
LEFT JOIN [VOTES]
ON VOTES.PRODUCT_ID = PRODUCTS.PRODUCT_ID AND
VOTES.USER_ID = 3
LEFT OUTER JOIN (SELECT Product_ID, SUM(V_Score) TotalScore FROM Votes GROUP BY Product_ID) TotalVotes ON Products.Product_ID = TotalVotes.Product_ID
WHERE PRODUCTS.STATUS_ID = 1
ORDER BY ISNULL(TotalScore,0) ASC
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply