By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,666 Members | 1,784 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,666 IT Pros & Developers. It's quick & easy.

Sorting results from one table by rows in another table

HaLo2FrEeEk
100+
P: 404
I've got a table where I store tips for a challenge. Its structure is this:

tip_id, challenge_id, user_id, ip_address, tip_date, tip_text

The tips can be voted up or down, I store vote data in another table, which looks like this:

tip_id, ip_address, vote_date, vote

The similar column between the two is tip_id. Vote is a bool, 0 means a vote down, 1 means a vote up.

My page will initially display one tip per challenge, but there will be a page that will show all tips for the specified challenge. In an effort to reduce my query count, I devised this query:

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.   V.vote AS vote,
  3.   T.tip_id AS tip_id,
  4.   T.tip_date AS tip_date,
  5.   T.tip_text AS tip_text
  6.   FROM votes V
  7.     LEFT JOIN tips T
  8.     ON V.tip_id = T.tip_id
  9.   WHERE T.challenge_id = 1
  10.   ORDER BY T.tip_id ASC
Which returns a new row for each vote and a copy of the tip data for each one. As an example, I inserted a test tip and 3 test votes, running this query returns 3 rows with the 3 votes, and 3 copies of the tip data, and I know that's how it's supposed to work. What I'd like to do is have it return one row per tip, with the count of up and down votes, sorted by the difference of up and down. By this I mean that if there are two tips, a and b:

a: 3 up, 2 down (diff: 1)
b: 4 up, 1 down (diff: 3)

Then b would come first and a would come second, since b has a higher overall score.

I really don't care how complicated the query is, I just can't think of how I could do it in PHP.

Oh, and in addition to the overall rating I also need the individual up and down values. Using the above example for b, I'd like this result:

Expand|Select|Wrap|Line Numbers
  1. up | down | rating | tip_id | tip_date | user_id | tip_text
  2. 4  | 1    | 3      | 1      | 12345678 | 1       | "This is some text"
Can someone suggest me a way to do this? It's sorta the defining feature of my project, letting people vote on submitted tips and having higher-rated tips appear higher in the list.

Edit: As a side note here's the query I'm currently using to get the up / down counts for a specified tip:

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.   count(CASE WHEN vote = 1 THEN 'up' ELSE NULL END) AS up,
  3.   count(CASE WHEN vote = 0 THEN 'down' ELSE NULL END) AS down
  4.   FROM votes
  5.   WHERE tip_id = 1
I tried modifying it a little to try to include everything from the tips table as well, like this:

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.   count(CASE WHEN V.vote = 1 THEN 'up' ELSE NULL END) AS up,
  3.   count(CASE WHEN V.vote = 0 THEN 'down' ELSE NULL END) AS down,
  4.   T.*
  5.   FROM votes V, tips T
  6.   WHERE V.tip_id = 1
And it sorta worked, but for some reason the up and down count are double what they should be, so if a tip has 2 up votes, the number displayed is actually 4. No clue why, any ideas?

Edit 2: I should also note that if a challenge ID has more than one tip associated with it, then the query should return each tip in it's own row. A problem I'm having right now is that I'm getting a total count of up and down votes for all tips for the specified challenge, in a single row. The problem with the two queries I posted in the first edit is that they perform the query based on the value of the tip_id, which means I can only get 1 tip at a time. I need to return all tips for the challenge, so I need to perform the query using the value of challenge_id.
Mar 22 '11 #1
Share this Question
Share on Google+
1 Reply


HaLo2FrEeEk
100+
P: 404
I just figured I'd post an answer here. Since no help was forthcoming, I asked the same question on StackOverflow and got the answer I was looking for. MY final query is a lot simpler than I expected it to be, and I'm really happy to be learning about [LEFT/INNER/OUTER] JOIN, it's so useful! Here's the query I'll be using:

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.   COUNT(CASE WHEN V.vote = 1 THEN 'up' ELSE NULL END) AS up,
  3.   COUNT(CASE WHEN V.vote = 0 THEN 'down' ELSE NULL END) AS down,
  4.   (COUNT(CASE WHEN V.vote = 1 THEN 'up' ELSE NULL END)-COUNT(CASE WHEN V.vote = 0 THEN 'down' ELSE NULL END)) AS rating,
  5.   T.*
  6.   FROM tips T
  7.   LEFT JOIN votes V
  8.   ON T.tip_id = V.tip_id
  9.   WHERE T.challenge_id = 10
  10.   GROUP BY T.tip_id
  11.   ORDER BY rating DESC
All I have to provide to it is the challenge_id, it'll do all the work from there. The best part is, because of the LEFT JOIN, tips that don't have any votes will still appear in the result, with their up, down, and rating fields set to 0.

I posted this so that someone in the future with a similar problem can have the resource.
Mar 24 '11 #2

Post your reply

Sign in to post your reply or Sign up for a free account.