Page 1 of 1

Join Queury Q. Can't Include Records With 0 Value

Posted: Sun Nov 01, 2009 3:48 pm
by jbh
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?

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 , 30
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

Re: Join Queury Q. Can't Include Records With 0 Value

Posted: Mon Nov 02, 2009 1:34 am
by VladSun
Use a LEFT JOIN.

Re: Join Queury Q. Can't Include Records With 0 Value

Posted: Tue Nov 03, 2009 9:44 am
by jbh
Doesn't work. It does the same thing; only display choices that were submitted in the poll, it doesn't show the choices not submitted in the poll with a 'null' or '0' value.

I know this was suggested on another forum as well but, to this day, I cannot figure it out.

Re: Join Queury Q. Can't Include Records With 0 Value

Posted: Thu Nov 05, 2009 3:09 pm
by jbh
Is this just one of those things that is due to a poor table design?
I can't find an answer anywhere and I am surprised. I rarely have this much trouble with joining records.