LEFT JOIN
On Sat, 11 Jan 2003 18:35:44 +0100, Blaster used a few recycled electrons to form: | 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
