Help with a query and sorting - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with a query and sorting

Author

Topic

imanta
Starting Member

6 Posts

Posted - 2006-02-08 : 12:05:04

I have three tables: one that holds users, on that holds products, and one that holds votes on products (fields are userid, productid, and the vote they made (1-5)). I am trying to come up with a query that orders the product result set by number of votes both descending and ascending for a particular user. If a user has not voted, then there will not be anything in the vote table for that user. I am not even sure where to begin on this as it is way out of my SQL experience.

This is a normal query I use to just pull them out ordered by name.

SELECT PRODUCTS.PRODUCT_ID, PRODUCTS.P_NAME, USERS.U_USERNAME
FROM [PRODUCTS]
INNER JOIN [USERS] ON USERS.USER_ID = PRODUCTS.USER_ID
WHERE STATUS_ID = 1
ORDER BY PRODUCTS.P_NAME DESC

Can anyone help or point me in the right direction?

Thanks.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-08 : 12:13:51

Can you give an example of the expected result set as I can't work out if you want this by vote (i.e 1 for every vote regardless of if it was 1,2,3,4 or 5) or whether the 1-5 matters..

sveroa
Starting Member

14 Posts

Posted - 2006-02-08 : 12:19:50

I would created a Vote table instead of the status_id in the product table, and done something similar as follows:

Table: product
product_id
p_name

Table: user
user_id
u_name

Table: vote
product_id
user_id
vote

SELECT a.product_id, a.p_name, count(c.vote)
FROM [product] a, [users] b, [vote] c
WHERE a.user_id = b.user_id
AND c.product_id = a.product_id
AND c.user_id = a.user_id
AND b.u_name = 'XXX'
GROUP BY a.product_id, a.p_name
ORDER BY c.vote DESC

Swap DESC with ASC for the ascending sorting

imanta
Starting Member

6 Posts

Posted - 2006-02-08 : 12:24:14

The status and vote thing are independent. Status is just whether a product should be visible to the user at all.

Basically in the end it should look like the following in terms of results (note that 0 denotes has not been voted on so it will not be in the Votes table).

User: RickD
Products:
Shirt - 5
Shoes - 3
Tie - 2
Belt - 1
Watch - 0
Pants - 0

OR

User: RickD
Products:
Watch - 0
Pants - 0
Belt - 1
Tie - 2
Shoes - 3
Shirt - 5

Thanks for the help.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-08 : 12:57:04

You will need to add the vote table into your query, i'm guessing that the votes are by productid, so that will be your link (you'll need to use a left join as you want all Products, then just ORDER BY Vote ASC or ORDER BY Vote DESC..

imanta
Starting Member

6 Posts

Posted - 2006-02-08 : 13:13:26

RickD, wow thanks for the info. Can you look at the following and see if it makes sense? As I said, very new to this. Here is a pass at it and I am getting results that look good...

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

One thing, when I do this, I am not getting the products that have not been voted for. Isn't that the purpose of a Left Join?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources