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?
Philippe Poelvoorde wrote:
> Michael Stassen wrote:
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;
What if the student has 2 result in maths (or 2 in chemistry) ??? ;-)
Then the table has redundant rows!
But seriously, that's a different question -- one which would require more
information to answer. In other words, based on the table description, I
assume the combination of StudentID and Subject is unique in table
SubjectGrade. While 'Maths', 'Phys', and 'Chem' sound categorical, I expect
they were simplifications of actual course names, and the question amounts to
finding students with 'A's in 2 specific courses. If the Subject is in fact
categorical, so that a given student may have several grades (rows) for the
same Subject, then I think we need more details. What, exactly, are thee
requirements? Do we want students with at least one 'A' in each category?
Students with all 'A's in each? Students with an 'A' average in each?
Something else?
The advantage of the GROUP BY version of the query (if my assumptions are
correct) is that it generalizes better than the self-join. For example, to
find students with an 'A' in 'Bio', 'Maths', 'Phys', and 'Chem', you'd have to
join the SubjectGrade table 2 more times in the self-join version, which gets
unwieldy and inefficient. The GROUP BY version is easy to change, however:
SELECT StudentID, StudentName
FROM Student s
JOIN SubjectGrade sg ON s.StudentID = sg.StudentID
WHERE sg.Subject IN ('Maths', 'Chem', 'Bio', 'Phys')
AND sg.Grade = 'A';
GROUP BY s.StudentID
HAVING COUNT(*) = 4;
You could also find students who have gotten 'A's in exactly 3, or at least 3,
of those 4 subjects simply by modifying the HAVING clause to COUNT(*)=3, or
COUNT(*)>=3, respectively.
On the other hand, to find a student with an 'A' in 'Chem' and a 'B' in
'Phys', for example, I think you'd probably use the self-join version, as you
need to tie the grade to the subject (though you could tie them with CONCAT in
the GROUP BY version, I suppose).
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
