Query for a poll- show all the options 1

[BigRed1212]

At the risk of getting in trouble, I am reposting this here. I accidentally posted it in the ASP formum even though it is an SQL question and I want to make sure it gets found.

Apologies in advance if I am breaking the rules too badly.
________________________________

I've written a "quick poll" that uses ASP to pull info from an Access database and drop a little poll into a Web page.

The database has three tables:

poll_questions with fields

poll_id
poll_question


poll_choices with fields

poll_id
choice
response_text


poll_data with fields

poll_id
ip
choice


All fields are type text because that is the way I did it.

poll_question holds the question for a poll_id. I currently just have 2 but want to add a new poll each week.
poll_choices holds the possible responses to each poll. choice is a '1','2','3','4', etc depending on how many possible answers.
poll_data are the votes that come in through the Web form. I capture the voter's IP and store it so each IP can only vote once.


I can do all that. Display the poll, show the radio buttons, collect the votes, switch views when someone has voted, show the results.

It's the results query I am having trouble with. I want a query that shows each poll option, even those not yet voted on, and the count of the votes for each option. I can write a query that shows the count for each option that has been voted on, but I don't know how to make it pick up all the options- even the ones with no votes.


Don't laugh at my query:

sql_5 = "select a.poll_id, a.choice, a.response_text, a.votes as votes, b.number_of_answers as answers, votes/answers as percenttt from 

(SELECT poll_choices.poll_id, poll_choices.choice, response_text, count(poll_data.choice) AS votes FROM poll_choices LEFT JOIN poll_data ON poll_choices.choice=poll_data.choice WHERE poll_choices.poll_id='" & poll_id & "' and poll_data.poll_id ='" & poll_id & "' GROUP BY poll_choices.poll_id, poll_choices.choice, poll_choices.response_text order by poll_choices.choice) a 

left join (select poll_id, count(poll_data.choice) as number_of_answers from poll_data where poll_id = '" & poll_id & "' and poll_data.poll_id ='" & poll_id & "' group by poll_id) b 

on a.poll_id = b.poll_id"

poll_id comes from the page or the querystring.

I want a zero to show for the vote count (votes) and a 0% to show for choices where there are no votes.

I imagine that will involve an iif statement- maybe "iif(answers > 0, votes/answers, '0%') as percenttt" but that doesn't solve my problem of showing all the options, even the ones not voted on.

SQL gurus, please help?

 

[randy700]

maybe this will help?
nz(a.votes,0) as votes


Randy

 

[BigRed1212]

NZ is so funky in ASP and Access. It never has worked for me. I see it recommended and it doesn't work.

"Microsoft JET Database Engine error '80040e14'
Undefined function 'nz' in expression."

I replaced nz(a.votes,0) with

iif(isnull(a.votes),0,a.votes)

Which didn't solve anything but which did get me thinking differently and led to:

SELECT 
a.poll_id, 
a.choice,
a.response_text,  
iif(isnull(b.count),0,b.count) as votes, 
c.number_of_answers as denominator,  
iif(denominator > 0, votes/denominator, 0) AS percenttt
FROM 

(
(select poll_id, choice, response_text from
poll_choices
where poll_id ='2'
order by choice) a

left join 

(SELECT choice, count(choice) as count
FROM poll_data
where poll_id='2'
group by choice) b

on a.choice = b.choice
)
left join 

(select poll_id, count(poll_data.choice) as number_of_answers 
from poll_data 
where poll_id = '2' and poll_data.poll_id ='2' 
group by poll_id) c

on a.poll_id = c.poll_id

order by a.choice

which seems a good deal cleaner. This runs in Access and gives me what I want. Now I just have to translate it into an ASP string statement and allow for different values of poll_id.

A star for the brain jog effect.