You might have to tweak the following a bit for syntax: SELECT a.name, COUNT(b.id) as votes FROM poll_options as a LEFT JOIN poll_votes as b ON a.id=b.oid WHERE b.pid='<poll_id>' GROUP BY b.oid
Also, you can refer to: http://www.mysql.com/doc/en/JOIN.html Bhavin. ----- Original Message ----- From: "Blaster" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, January 11, 2003 12:35 PM Subject: SQL optimization problem > Hey, > > (this post is pretty long, a short version of the problem is listed at the > bottom if you don't like reading long emails :P) > > I'm currently going through all my SQL queries for my webpage to see if > there is anything I can do to optimize them. > On my webpage, i have this poll where people can give their opinion in > various subjects by casting a vote. To begin > with, I'd like to tell you how I created my tables for this task, the poll > uses in total 3 different tables as following: > > poll_list (this table contains the actual question of each poll) > =========================================== > id (int) | stamp (datetime) | question (varchar 255) | active (tinyint) > > Id is simply an autoincrementing ID for each poll, > > Stamp is the creation date of the poll, > > Question holds the actual question (duh :P) > > If Active is 1, it means that this is the active poll right now. > Only one poll can be active at the same time. > > poll_options (this table holds the valid answers for each poll. You may use > any number of answers in your poll) > ============================================================================ ==== > id (int) | pid (int) | name (varchar 255) > > id is again, autoinc field for this answer > > pid is a pointer to which poll this particular answer belongs to, i.e pid = > poll_list.id > > name holds the actual answer string > > poll_votes (this table holds all the casted votes, one row is one vote) > ================================================= > id (int) | pid (int) | oid (int) | uid (int) > > id, autoinc > > pid, pointer to poll_list.id, tells me which poll this vote belongs to > > oid, pointer to poll_options.id, tells me which option this user voted > > uid, pointer to user account. I won't include the user table, just think of > this as a unique identifier > for the users, prevents the same user from voting twice in a poll.. > > > AND now! to the problem! Prior to my "optimzation" checking began, the code > to display the > results of a poll was something like this: > > 1) Fetch the active poll: > SELECT * FROM poll_list WHERE (active > 0) ORDER BY stamp DESC LIMIT 1 > > 2) Fetch the answers for the poll ID we received from the prior query: > SELECT * FROM poll_options WHERE pid='<id_from_prior_query>' > > 3) For each option received in step 2, I did: > SELECT * FROM poll_votes WHERE pid='<poll_id>' AND oid='<option_id>' > > 4) Output HTML formated code to web visitor. > > Now, I thought, it MUST be possible to make step 2 and 3 using 1 single > query, because using this old > system (as shown above), it requires 1 + n queries, where n is number of > answers in that particular poll. > > So, I simply replaced it with: > > 1) Fetch the active poll: > SELECT * FROM poll_list WHERE (active > 0) ORDER BY stamp DESC LIMIT 1 > > 2) Fetch the answers & votes in the same query: > SELECT a.name, COUNT(b.id) as votes FROM poll_options as a, poll_votes as b > WHERE b.pid='<poll_id>' AND b.oid=a.id GROUP BY b.oid > > 3) Print the results. > > #################### PROBLEM BEGINS HERE ########################## > > However! Here comes the problem, if no vote is cast on an option, it will > not show up in the list! I want it to print 0% > for any options that havn't received a vote, like it would with my old > query system. This is basically what I want to > do: > > Select all options from poll_options and, in the same query, count the > number of rows in poll_votes which has that > particular options "id" as "oid". Pretty hard to explain, but ideally, I'd > like to do > > SELECT a.name, COUNT(b.id) as votes FROM poll_options as a, poll_votes as b > WHERE b.pid='<poll_id>' AND b.oid=a.id GROUP BY b.oid > > With one exception, if votes = 0, it should be listed in the result aswell! > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/doc/ (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
