CODEWITHSUNDEEP
phpmysqlsql
Malarky44
Malarky44

Reputation: 45

Query two tables together, only one record displaying

I am building a polling system and I have a query right now that is supposed to select all of the polls and count all of the votes for each of the polls (which lie in a separate table). So my tables look like:

Polls:  
ID  
Title  
Body

Votes:  
ID  
PollID  
Vote  (This value is either 0 or 1)

Well the totaling of the votes looks like it is working, the issue is that it is currently only displaying one record.

Currently my query looks like this:

SELECT POLLS.ID, 
       POLLS.TITLE, 
       POLLS.BODY, 
       Sum(CASE 
             WHEN VOTES.VOTE = 1 
                  AND VOTES.POLLID = POLLS.ID THEN 1 
             ELSE 0 
           END) AS yay, 
       Sum(CASE 
             WHEN VOTES.VOTE = 0 
                  AND VOTES.POLLID = POLLS.ID THEN 1 
             ELSE 0 
           END) AS nay, 
       FROM     polls, 
       VOTES 
ORDER  BY POLLS.ID

Also I am using PHP with Codeigniter.

Upvotes: 1

Views: 121

Answers (4)

taufique
taufique

Reputation: 2751

What you are missing is "GROUP BY". Use GROUP BY PollID while taking sum.

Upvotes: 0

Stephen O'Flynn
Stephen O'Flynn

Reputation: 2329

I'd go with:

    SELECT polls.ID, polls.title, polls.body,
    SUM(IF(votes.vote = 1, 1, 0)) AS yay, 
    SUM(IF(votes.vote = 0, 1, 0)) AS nay
    FROM polls
    JOIN votes ON (votes.pollID = polls.ID)
    GROUP BY ID, title, body

Upvotes: 0

Ray
Ray

Reputation: 41428

SELECT polls.ID,polls.title,polls.body,
  SUM(case when votes.vote = 1 then 1 else 0 end) AS yay,
  SUM(case when votes.vote = 0 then 1 else 0 end) AS nay,
FROM polls 
JOIN votes ON polls.ID = votes.pollID
  GROUP BY poll.ID, polls.title, polls.body
  ORDER BY polls.ID

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79909

You need to GROUP BY pollID the values in the votes table:

SELECT p.ID, p.title, p.body, v.yay, v.nay
FROM
(
    SELECT PollID,
      SUM(CASE WHEN vote = 1 THEN 1 ELSE 0 END) AS yay,
      SUM(CASE WHEN vote = 0 THEN 1 ELSE 0 END) AS nay,
    FROM votes
    GROUP BY PollID
) v
INNER JOIN Polls p ON v.PollID = p.Id
ORDER BY p.ID

Upvotes: 0

Related Questions