Join two tables in MySQL

Question

I have the following table with voices:

I am trying to join a list of items, with a user table and this vote table.

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

This query gives me the following:

I would like to get the total number of votes for each list_item, as well as a column for up_votes and another for down_votes . And, of course, I do not want item_id to repeat this way.

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

EDIT: Here is 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 hard part is two sum calls. If the voting field is 1 , then vote=1 , which evaluates to TRUE, which MySQL will discard as an integer 1 for the purposes of SUM (). If it is not 1, then it evaluates to false, which is cast to 0 and does nothing for SUM ().

---

screams must have

 GROUP BY list_items.item.id 

in the end.

Answer 2

Try:

  SELECT li.item_id, li.text, li.date_added, u.username, SUM(IF(v.vote = 1, 1, 0)) up_votes, SUM(IF(v.vote = -1, 1, 0)) down_votes, COUNT(v.vote) total_votes FROM list_items li INNER JOIN users u ON li.user_id = u.id INNER JOIN votes v ON li.item_id = v.item_id WHERE li.list_id = 3 GROUP BY li.item_id 

Suppose the user id column has the id name in your users table.