Join Queury Q. Can't Include Records With 0 Value
Posted: Sun Nov 01, 2009 3:48 pm
Hey. I have a poll system and, when a user wants to view results, obviously they need to view all options and their % of votes. Well, if I have 3 options (choices) to a question, but only 2 are answered, I still need to be able to display the *un-answered* option and show null or 0 for the # of votes. Problem is, my query only pulls ANSWERED options and their total votes. How can I improve my query to show ALL options, even if NONE are voted for?
For reference only, if you wish to have a visual aid to my dilemma, below is the table structure of the key tables and the existing and desired output I am looking for.
Table poll_results:
ID | choice_id | question_id
1 1 1
2 2 1
Table poll_choices:
ID | choice | question_id
1 Yes 1
2 No 1
3 Maybe 1
Table poll_questions:
ID | title
1 Test Question
This shows what it would look like if I create 1 question, add 3 options and users
submit a poll response 1 time for each response (2 total votes, one for each)
The idea is that I already have a query that can pull results that EXIST in the db, but, for some reason, I can't get this printout:
Output:
choice | total_votes
1 1
2 1
3 0 (or null)
Now it only shows:
choice | total_votes
1 1
2 1
Code: Select all
SELECT choice, count( poll_results.choice_id ) AS total_answers,poll_results.user_id as poll_taker FROM poll_choices
JOIN poll_results ON ( poll_results.choice_id = poll_choices.ID )
join poll_members ON (poll_members.ID=poll_results.user_id)
WHERE poll_results.question_id = '1'
GROUP BY poll_choices.ID
ORDER BY total_answers LIMIT 0 , 30Table poll_results:
ID | choice_id | question_id
1 1 1
2 2 1
Table poll_choices:
ID | choice | question_id
1 Yes 1
2 No 1
3 Maybe 1
Table poll_questions:
ID | title
1 Test Question
This shows what it would look like if I create 1 question, add 3 options and users
submit a poll response 1 time for each response (2 total votes, one for each)
The idea is that I already have a query that can pull results that EXIST in the db, but, for some reason, I can't get this printout:
Output:
choice | total_votes
1 1
2 1
3 0 (or null)
Now it only shows:
choice | total_votes
1 1
2 1