Reputation: 319
I have 3 tables
1)question
2)options
3)answers
Table 1 contains list of multiple choice questions. Table 2 contains multiple choices for the question.
Consider an example, I have the question "Which game do you like most?"
It is stored in table 1 with id 1. For this question there are 3 choices in table 2; as "cricket", "football", "tennis" with ids 1,2 & 3.
When a user answer this question the question id, and option id are stored to the third table as if he select football, entry in table 3 is question id and option id.
If another user select same option new entry is stored in table 3.
My need is that I want to get the count of each options in table 3.
Consider 10 users select cricket, 15 users select football, no user select tennis, so I need count as 10,15,0 with its corresponding option id
Table name: questions
--------------------------------
| id | question |
--------------------------------
| 1 | which game u like most |
Table Name: options
------------------------------------------------
| id | qid | option_name |
------------------------------------------------
| 1 | 1 | cricket |
------------------------------------------------
| 2 | 1 | football |
------------------------------------------------
| 3 | 1 | tennis |
------------------------------------------------
Table Name: answers
--------------------------------------------
| id | qid | optionId |
--------------------------------------------
| 1 | 1 | 3 |
---------------------------------------------
| 2 | 1 | 3 |
----------------------------------------------
| 3 | 1 | 2 |
----------------------------------------------
The above table means that, 2 people choose tennis, 1 people choose football and no one choose cricket. So I need result table as
------------------------------------------------------
| id | question | option_name | count |
------------------------------------------------------
| 1 | which game u like most | cricket | 0 |
-------------------------------------------------------
| 2 | which game u like most | football | 1 |
-------------------------------------------------------
| 3 | which game u like most | tennis | 2 |
-------------------------------------------------------
But when I tried, I didnt get the count for cricket, because no one selected cricket.I must get count of cricket as 0. Can any one help me solve the issue? My sql code is
SELECT count(an.optionId) count , op.option_name, q.question from
questions q, options op, answers an where q.id=1
and q.id=op.qid
and op.id=an.optionId
group by q.question, op.option_name
Upvotes: 1
Views: 47
Reputation: 40481
Just use a LEFT JOIN
and COALESCE()
:
SELECT COALESCE(count(an.optionId),0) as count , op.option_name, q.question from
questions q
INNER JOIN options op
ON(q.id=op.qid )
LEFT OUTER JOIN answers an
ON(p.id=an.optionId)
where q.id=1
group by q.question, op.option_name
Please avoid the use of implicit join syntax(comma separated ) and use only the correct syntax of a join! LEFT JOIN
with implicit syntax getting more hard to read and more easy to make mistakjes.
Upvotes: 2