You cna fix this problem with the "HAVING" keyword in the WHERE clause.
HAVING is like WHERE, but on group-by functions, like COUNT(*). Change the query to: SELECT id,name,userid,correct,count(correct) as numcorrect FROM users,answers WHERE id=userid AND correct='true' GROUP BY id HAVING count(correct)=4 This should work (*although I have not tried it yet*). Jamie Beu Lockheed-Martin Information Systems Software Engineer CTIA (407) 306-2484 "The true soldier fights not because he hates what is in front of him, but because he loves what is behind him." G. K. Chesterton - Illustrated London News, 1/14/11 -----Original Message----- From: Tom Emerson [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 05, 2002 11:43 AM To: [EMAIL PROTECTED] Subject: RE: query problem I am by no means an "SQL-expert", but I'll give this a shot... :) > -----Original Message----- > From: Richard Brenner [mailto:[EMAIL PROTECTED]] > Subject: query problem > > I have two tables with the following structure: > > Users: > | Field | Type > | id | int(10) unsigned > | name | blob [etc] > +--------------+------------------ > answers: > +----------+---------------------+ > | id | int(10) unsigned | > | userid | int(10) unsigned | > | question | int(10) unsigned | > | correct | set('true','false') | > | date | date | > +----------+---------------------+ > This is for a quiz. Every user has to answer 4 questions ... > I want to print out all users, that > have answered all 4 questions correct. > Can I do this with one query? I built a couple of tables similar to what you have [trimmed to the "essentials"], populated with some data, and ran the following: SELECT id,name,userid,correct,count(correct) as numcorrect FROM users,answers WHERE id=userid AND correct='true' GROUP BY id which created: +----+---------+--------+---------+------------+ | id | name | userid | correct | numcorrect | +----+---------+--------+---------+------------+ | 1 | alfred | 1 | true | 3 | | 2 | bobby | 2 | true | 4 | | 3 | carol | 3 | true | 2 | | 4 | diane | 4 | true | 3 | | 5 | edward | 5 | true | 3 | | 6 | frank | 6 | true | 3 | | 7 | george | 7 | true | 1 | | 8 | harry | 8 | true | 3 | | 9 | larry | 9 | true | 1 | | 10 | mark | 10 | true | 3 | | 11 | nancy | 11 | true | 4 | | 12 | oliver | 12 | true | 3 | | 13 | paul | 13 | true | 2 | | 14 | quentin | 14 | true | 4 | | 15 | ralph | 15 | true | 3 | | 16 | samuel | 16 | true | 3 | | 17 | thomas | 17 | true | 1 | | 18 | ursula | 18 | true | 4 | | 19 | victor | 19 | true | 2 | | 20 | walter | 20 | true | 3 | | 21 | xavier | 21 | true | 4 | | 22 | yvonne | 22 | true | 3 | | 23 | zack | 23 | true | 2 | | 24 | igor | 24 | true | 3 | | 25 | jack | 25 | true | 4 | | 26 | kristen | 26 | true | 4 | +----+---------+--------+---------+------------+ unfortunately, adding "and numcorrect=4" to the WHERE clause caused an error (numcorrect undefined), but if this were put into a temporary table, it is then trivial to SELECT * FROM results WHERE numcorrect=4; --------------------------------------------------------------------- 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 <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- 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 <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
