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]
