[thelist] SQL query help
Erik Mattheis
gozz at gozz.com
Tue Jan 1 21:41:48 CST 2002
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.
I got as far as:
SELECT TOP 1 polls.poll_date, polls.poll_question, poll_options.option_text
FROM polls, poll_options
WHERE polls.poll_active = 0 AND
poll_options.poll_id = polls.poll_id
ORDER BY polls.poll_date DESC
(this is for SQL Server 7)
Can I add some SQL so it returns the option_text that got the most votes?
I could just grab a COUNT of poll_answers.poll_option_id and loop
through it in ColdFusion and only output the row with the greatest
number of votes ... but I'd rather do it in SQL ... or is there some
SQL that would increment a field in poll_options each time someone
votes?
--
__________________________________________
- Erik Mattheis
(612) 377 2272
http://goZz.com/
__________________________________________
More information about the thelist
mailing list