Jason Chan wrote:
I have a student Table and a SubjectGrade table
Create Table Student(
StudentID INT NOT NULL,
StudentName VARCHAR(30)
)
Create Table SubjectGrade(
StudentID INT NOT NULL,
Subject VARCHAR(30) NOT NULL,
Grade CHAR(1)
)
let's say have following record in SubjectGrade
1 Maths A
1 Phys B
1 Chem A
2 Maths A
2 Chem A
3 Bio C
3 Chem A
I want to find out students who have got A in both Maths and Chem
How the SQL look like?
Use a self-join on SubjectGrade, requiring an A in Maths on the left and an A
in Chem on the right, like this:
SELECT StudentID, StudentName
FROM Student s
JOIN SubjectGrade sg1 ON s.StudentID = sg1.StudentID
JOIN SubjectGrade sg2 ON s.StudentID = sg2.StudentID
WHERE sg1.Subject = 'Maths' AND sg1.Grade = 'A'
AND sg2.Subject = 'Chem' AND sg2.Grade = 'A';
Alternatively, use an IN list for the subject, then use a HAVING clause to
limit the results to students with 2 matching rows, like this:
SELECT StudentID, StudentName
FROM Student s
JOIN SubjectGrade sg ON s.StudentID = sg.StudentID
WHERE sg.Subject IN ('Maths', 'Chem')
AND sg.Grade = 'A';
GROUP BY s.StudentID
HAVING COUNT(*) = 2;
Change "GROUP BY s.StudentID" to "GROUP BY s.StudentName" if you want the
results in alphabetical order by name instead of in ID order.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
