Sulu.MeanStack Sulu.MeanStack - 5 months ago 10
SQL Question

Getting count byjoining multiple tables

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

Answer

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.