Sorting results from one table by rows in another table

[HaLo2FrEeEk]

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:

SELECT
  V.vote AS vote,
  T.tip_id AS tip_id,
  T.tip_date AS tip_date,
  T.tip_text AS tip_text
  FROM votes V
    LEFT JOIN tips T
    ON V.tip_id = T.tip_id
  WHERE T.challenge_id = 1
  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:

up | down | rating | tip_id | tip_date | user_id | tip_text
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:

SELECT
  count(CASE WHEN vote = 1 THEN 'up' ELSE NULL END) AS up,
  count(CASE WHEN vote = 0 THEN 'down' ELSE NULL END) AS down
  FROM votes
  WHERE tip_id = 1

I tried modifying it a little to try to include everything from the tips table as well, like this:

SELECT
  count(CASE WHEN V.vote = 1 THEN 'up' ELSE NULL END) AS up,
  count(CASE WHEN V.vote = 0 THEN 'down' ELSE NULL END) AS down,
  T.*
  FROM votes V, tips T
  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.

[HaLo2FrEeEk]

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:

SELECT
  COUNT(CASE WHEN V.vote = 1 THEN 'up' ELSE NULL END) AS up,
  COUNT(CASE WHEN V.vote = 0 THEN 'down' ELSE NULL END) AS down,
  (COUNT(CASE WHEN V.vote = 1 THEN 'up' ELSE NULL END)-COUNT(CASE WHEN V.vote = 0 THEN 'down' ELSE NULL END)) AS rating,
  T.*
  FROM tips T
  LEFT JOIN votes V
  ON T.tip_id = V.tip_id
  WHERE T.challenge_id = 10
  GROUP BY T.tip_id
  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.