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.
>SELECT p.p_question, a.pa_answer, COUNT(r.pv_poll_answer) AS total
>FROM fsnep_polls p, fsnep_pollAnswers a, fsnep_pollresults r
>WHERE p.p_id = a.pa_pollID
>AND a.pa_id = r.pv_poll_answer
>GROUP BY p.p_question, a.pa_answer
>ORDER BY p.p_question, a.pa_answer
>
><cfoutput query="myquery" group="p_question">
><p>
>#p_question#<br>
><cfoutput>
>#pa_answer# = #total#
></cfoutput>
></p>
></cfoutput>
>
>But, I think that you'll find the more relational stuff you do,
>you'll want to avoid cryptic column names - for example, you'll say "okay,
>did p_id mean pollid? or did it mean partyid? or personid? or somethine
>else?
understood.
>Also, I make it a habit of using the same column name for the foreign
>key. In other words, if you have pollid in polls, then you can have pollid
>in pollanswers, instead of changing it to pa_pollid. (You also don't need to
>prepend the table abbreviation to all columns that way.)
I was advised to do both of these after someone looked at my code, but it'd be no problem to switch back to the way I used to do it.
>Finally, I'm not sure why you have a foreign key in the results table to the
>poll table. The only reason you'd need that is if you were planning on using
>the same questions for multiple polls (in which case the example query above
>needs another where clause).
I'm not sure either. I bet that I had a reason when I did it, but that doesn't make it a sound reason.
So any other help on the query would be useful. I appreciate the clarity so far.
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
