Let's walk through what the query is doing....
SELECT p.p_question, a.pa_answer, COUNT(r.pv_poll_answer) AS total <!--- Get
each question, each answer, and the total number of responses for each
answer --->
FROM fsnep_polls p, fsnep_pollAnswers a, fsnep_pollresults r <!--- we're
gathering info from these three tables --->
WHERE p.p_id = a.pa_pollID <!--- The pollid in your poll table should be
the same poll id in your answer table, so that answers are linked with the
poll --->
AND a.pa_id = r.pv_poll_answer <!--- The answerid in your answer
table should be the same answerid as in your results table, so that we can
count how many results go with each answer --->
AND p.p_id = 21 <!--- You add an extra "where clause" if you want a
specific poll - this will filter out the answers and results from the other
polls, because of the joins above --->
GROUP BY p.p_question, a.pa_answer <!--- For the totals, we want to make
sure that we're counting the total for each question for each poll, not
totalling all the answers together --->
ORDER BY p.p_question, a.pa_answer <!--- We need to order this way so that
the CF output will group correctly --->
(I looked at the output and it looks like you already figured this out. But,
so you have the answer.... I've been away for a couple of days. Sorry.)
----- Original Message -----
From: "daniel kessler"
> well, I'm still a bit confused.
> Using your query, which I mostly understand, both of the two test polls
> that I have come up
> http://eatsmart.umd.edu/home/poll_results.cfm?poll=21
> though with the correct counts. Unfortunately I only want the one that
> matches the poll ID. I tried changing out:
> WHERE p.p_id = a.pa_pollID
> for
> WHERE p.p_id = 21 <!--- a known poll id --->
>
> and in that case it brings up only one poll, but when I add up the answer
> counts they're the total number for the two polls.
>
> Still, I don't know why more than one poll comes up initially, nor have I
> figured out how to make the poll counts accurate when I make sure there's
> only one poll queried by inserting the poll ID.
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
