CODEWITHSUNDEEP
mysql
N N
N N

Reputation: 1638

MYSQL getting poll results table

I have 2 tables, one with poll answers and one with votes:

DESC polls;

Field   Type      Null  Key Default Extra
id      int(11)   NO    PRI NULL    auto_increment
pollId  int(11)   NO    MUL NULL    
answer  varchar(150)    YES NULL    

DESC votes;
Field    Type      Null Key Default Extra
id       int(11)    NO  PRI NULL    auto_increment
pollId   int(11)    NO  MUL NULL    
answerId int(11)    NO  MUL NULL    
userId   int(11)    NO  MUL NULL

I am trying to get following results with all answers and votes:

pollId answerId numberOfVotes
1        1          20
1        2          10
1        3          0

I tried right joins of votes to answers, but it does not work:

SELECT answers.id, COUNT(votes.answerId) FROM answers JOIN votes ON votes.pollId = answers.pollId GROUP BY votes.pollId;

Upvotes: 1

Views: 110

Answers (1)

Fahmi
Fahmi

Reputation: 37483

use left join

DEMO

SELECT polls.pollId,polls.id,COUNT(userid) as counts
FROM polls left JOIN votes ON votes.pollId = polls.pollId and 
polls.id=votes.answerId
GROUP BY polls.pollId,polls.id order by polls.id

OUTPUT:

pollId  id  counts
14      17  0
14      18  2
14      19  0
14      20  0

Upvotes: 1

Related Questions