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
union all
select p.p_id,p.p_question, a.pA_answer, 0 AS total
FROM fsnep_polls p, fsnep_pollAnswers a
WHERE p.p_id = a.pA_pollID
AND p.p_id = #url.poll#
AND not exists (select 1 from fsnep_pollresults r where a.pA_id = r.pV_poll_answer)
order by 2,3,1
Thank you for replying. I read through your sql and understand it. I'm learning alot each time I do one of these. This is my first relational and it sure seem to complicate the query side of the db work.
>The query has to select the aggregrate and then outer join the related table.
>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]
