This is just a stab in the dark as to how the query would look, but if you
can see what I'm trying to do, you can follow the concept to completion. I'm
not a DBA so take this as a guideline and not the solution (unless, of
course, it *is* the solution in which case feel free to praise me)
<cfquery name="poll_results" datasource="eatsmart">
SELECT p.p_id,p.p_question, a.pA_answer, COUNT(r.pV_poll_answer) AS total
FROM fsnep_polls p
INNER JOIN rfsnep_pollAnswers a ON a.pA_pollID = p.p_id
OUTER JOIN fsnep_pollresults r ON r.pV_poll_answer = a.pA_id
WHERE p.p_id = #url.poll#
GROUP BY p.p_question, a.pA_answer, p.p_id
ORDER BY p.p_question, a.pA_answer
</cfquery>
________________________________
From: Daniel Kessler [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 22, 2004 11:26 AM
To: CF-Talk
Subject: Query/DB design problem
I recently asked how to structure a query to request poll results
from my db. I've listed the db structure and the query below.
The problem that I now have is that it only lists poll answers that
have been answered once. I know that this is because it's querying
the Results table (fsnep_pollresults) and that if there are answers
that have not been selected, then they won't show up in the results
table.
I don't know of a good, clean way of making this query include them.
The only way I know to do it is to get a list of the answers and set
up a query that looks for the count of each of the answers. So if
there are 5 answers, I'll have 5 queries. It sounds like a resource
hog to me.
Any thoughts?
Query:
<cfquery name="poll_results" datasource="eatsmart">
SELECT p.p_id,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
AND p.p_id = #url.poll#
GROUP BY p.p_question, a.pA_answer, p.p_id
ORDER BY p.p_question, a.pA_answer, p.p_id
</cfquery>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
