sql - Joining two tables in a MySQL

Question

I have the following table called votes:

I'm trying to join a list of items, with a users table, and this votes table.

      SELECT list_items.item_id, text, date_added, username 
        FROM list_items 
NATURAL JOIN users, votes 
       WHERE list_id = 3

That query is giving me this:

I would like to get a total vote count for each list_item, as well a column for up_votes and another for down_votes. And, of course, I don't want the item_id's to repeat like that.

I tried combining SUM with IF as explained in a Nettuts+ video, but the tutorial was too simple.

EDIT: Here's the list_items table:

Answer 1

SELECT list_items.text, list_items.item_id, SUM(votes.vote=1) AS upvote, SUM(votes.vote=-1) AS downvote
FROM list_items
LEFT JOIN votes ON list_items.item_id = votes.item_id

The tricky part are the two sum calls - If the vote field is 1, then vote=1 which evaluates to TRUE, which MySQL will cast to an integer 1 for the purposes of the SUM(). If it's not 1, then it evaluates to false which is cast to a 0 and doesn't do anything for the SUM().

---

whoops, needs to have

GROUP BY list_items.item.id

at the end.