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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jbh
Forum Commoner
Posts: 89
Joined: Tue Dec 05, 2006 7:01 pm

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

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post by VladSun »

Use a LEFT JOIN.
There are 10 types of people in this world, those who understand binary and those who don't
jbh
Forum Commoner
Posts: 89
Joined: Tue Dec 05, 2006 7:01 pm

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

Post 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.
jbh
Forum Commoner
Posts: 89
Joined: Tue Dec 05, 2006 7:01 pm

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

Post 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.
Post Reply