[thelist] SQL query help

Warden, Matt mwarden at mattwarden.com
Wed Jan 2 01:03:58 CST 2002


On Jan 1, Erik Mattheis had something to say about [thelist] SQL query help

>I'm trying to think of a way to do this and it's not immediately coming to me:
>
>Three tables: polls, poll_options, poll_results
>
>Polls:
>poll_id, poll_question, poll_date, poll_active
>
>poll_options:
>option_id, poll_id, option_text
>
>poll_answers:
>poll_answer_id, poll_id, option_id
>
>Each time a vote is cast, a row is added to the poll_answers table
>
>
>I need to come up with a query that selects the option that got the 
>most votes from the most recent poll where poll_active=0. In other 
>words, the winner of last week's poll.

Well, wait a minute here. Is there some reason you need poll answers to be
individual records like that? if not, i see your problem going away if you
change poll_answers to:

poll_answers
poll_answer_id, option_id, hitcount

Note that I axed the poll_id field. It's redundant because an option is
already unique to a poll (see poll_options). You might consider making
options unrelated to polls by getting rid of the poll_id field in the
options table and keeping the one in the answers table. That way you can
reuse options in multiple polls, but (more importantly, maybe) it makes
your "get winning option" sql much cleaner because you won't have a join
on the options table. However, if you have to join it anyway to get the
option_text, this really isn't much help. Just a thought. I also named
it "hitcount" rather than "count" because "count" is probably a reserved
word.

personally, if i made this change, i'd change the name to something like
"poll_result" (or "poll_results" -- we won't go there today, i guess ;-)

so:

poll_result
poll_result_id, option_id, hitcount

Then, when you were recording a vote, you'd just do this:

UPDATE poll_result SET hitcount=(hitcount+1) WHERE option_id=#form.foo#

And, really, if you wanted to, you could move the hitcount field up to the
option table and get rid of the result table altogether. But, then you're
mixing result data with poll data. Your call.

And, I see rudy posted a nice (as usual) response about extracting the
"winner", so, unless you decide to go with this (that will change how you
select the winning option), you're set.


--
mattwarden
mattwarden.com





More information about the thelist mailing list