you want, then put it in CF with a CFDUMP, then workout the pretty
output of the data. Working on a few assumptions here perhaps, but
take a stab at this
SELECT r.*, a.*, p.*
FROM fsnep_pollResults r, fsnep_pollAnswers a , fsnep_polls p
WHERE p.p_id = r.pv_poll_id
AND p.p_id = a.pA_pollid
AND p.p_id = '#url.poll#'
run this in SQL+ first if you have it, otherwise in CF and just use
CFDUMP to see what the query is giving you. Should give you all
columns from all tables for a poll. Now, narrow down the columns you
want in teh SELECT line.
HTH
Doug
----- Original Message -----
From: daniel kessler <[EMAIL PROTECTED]>
Date: Mon, 20 Sep 2004 09:46:20 -0400
Subject: relational display
To: CF-Talk <[EMAIL PROTECTED]>
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]
