This might work ... but I don't have a suitable database to test with ... select * from students,grades where students.id=grades.id and min(grades.grade - students.average_grade) > 0 group by students.id
Tom Haapanen [EMAIL PROTECTED] -----Original Message----- From: David Hugh-Jones [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 23 October, 2001 13:10 To: [EMAIL PROTECTED] Subject: selecting rows where all rows meet a criterion OK, I have a tricky SQL syntax problem I would like to share. For simplicity's sake, I'll use an analogy. I have two tables, of students and grades, each with a "ID" column for use in joins, corresponding to a student's ID. The student table has an "average grade" column which shows that student's average grade last year. The "grades" table has a column for subject (e.g. English, Math) and a column for the grade achieved. Suppose I want to select all my students who have performed very well this year. I want to select all the students who have beaten last year's average in ALL of this years marks! SELECT * FROM students,grades WHERE students.ID=grades.ID AND grades.grade > students.average_grade This doesn't work. It gets me all the occasions where a student has beaten his average. But I only want students who have never gone below their average! In other words, I want to select from the students table, based on information on multiple rows in the grades table, where ALL the grades rows need to fulfil a certain criterion. Help would be gratefully received! -- Dave --------------------------------------------------------------------- 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
