Nonetheless, I'll stick it out because your query gave me the closest to what I wanted. It actually grabbed all the polls that I had, not just the one, but I haven't read all that you wrote. It's lunch time and I want to read what you wrote with a clear head.
just as an fyi, this is what I received from that query:
http://eatsmart.umd.edu/home/poll_results.cfm?poll=21
Obviously I have alot to learn.
>Dan,
>I use 8i syntax, so no inner joins, but here's how I'd do it:
>
>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>
>
>As a side note - I loathe your naming conventions. There's a big ol' debate
>about whether tables should be singular or plural - that doesn't bother me
>so much. 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? 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.)
>
>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). In general (and this is a big generality) if
>you draw your ER diagram and there's a circle, you should think about
>whether or not you're using relationships correctly.
>
>-d
>
>
>
>
>----- Original Message -----
>From: "daniel kessler" <[EMAIL PROTECTED]>
>To: "CF-Talk" <[EMAIL PROTECTED]>
>Sent: Monday, September 20, 2004 8:43 AM
>Subject: relational display
>
>
>OK, I've managed to make relational tables in an O-racle database (see
>below) and I've managed to populate it with questions, answers, and results.
>Now, I wish to present the poll results. I've never displayed relational
>information. First trick is gathering it, it seems and the second is to
>output it. I think that I am gathering it correctly (not sure), but am at a
>loss how to display it. Here's the query that I've attempted:
>
><cfquery name="poll_results" datasource="eatsmart">
>SELECT p_question FROM fsnep_polls
>INNER JOIN fsnep_pollAnswers
> ON fsnep_pollAnswers.pA_pollID = #URL.poll#
>INNER JOIN fsnep_pollResults
> ON fsnep_pollResults.pV_poll_id = #URL.poll#
>WHERE p_id = #URL.poll#
></cfquery>
>
>But how do I specify that from fsnep_pollAnswers I want the field
>"pA_answer"?
>I tried outputting "pA_pollID", which seems to be gathered in this query and
>it gave me "not defined"
>
>Also, once I have the info, I'm not sure how to reference it to display it.
>I'm looking for a table that outputs:
>
>poll question
>
>answer1
>[count] [percentage]
>
>answer2
>[count] [percentage]
>
>etc.
>
>I guess I can group the query by "answer", but am not sure how to find out
>how many in a group. Should I just iterate through the records and initiate
>my own counts through variables in a loop?
>
>Any help would be greatly appreciated.
>
>The Tables:
>
><!--- the poll info, yknow --->
>create table fsnep_polls (
>p_id NUMBER Primary Key,
>p_date_added date,
>p_date_last_used date,
>p_question VARCHAR2(400),
>p_status NUMBER
>)
>
>
><!--- all possible answers for any poll available --->
>create table fsnep_pollAnswers (
>pA_id NUMBER Primary Key,
>pA_pollID NUMBER REFERENCES fsnep_polls(p_id) ON DELETE CASCADE,
>pA_answer VARCHAR2(400)
>)
>
>
><!--- all votes with their poll and answer ID --->
>create table fsnep_pollResults (
>pV_id NUMBER Primary Key,
>pV_date_added date,
>pV_poll_id NUMBER REFERENCES fsnep_polls(p_id),
>pV_poll_Answer NUMBER REFERENCES fsnep_pollAnswers(pA_id) ON DELETE CASCADE,
>pV_ip VARCHAR2(20)
>)
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
