CODEWITHSUNDEEP
mysqlsqlselectjoinhaving
Cratylus
Cratylus

Reputation: 54074

How does this query with HAVING MAX actually works correctly?

I have 2 simple tables: students(sno,sname,age) and take(sno,cno). Take is the table of a N-N relationship between students and courses.
I want to find students NOT taking a specific course.

The following query does the job but it is not clear to me how it works: 

SELECT s.sno,s.sname,s.age  
FROM students s LEFT JOIN take t  
ON (s.sno = t.sno)  
GROUP BY s.sno,s.sname,s.age  
HAVING MAX(CASE WHEN t.cno = 'CS112' THEN 1 ELSE 0 END) = 0;

Does it have to do with the order the MAX AND HAVING are processed?

A trivial way to do this would be with the subquery: 

SELECT * FROM students  
WHERE sno NOT IN  
(SELECT sno FROM take WHERE cno = 'CS112');

But I am interested in understanding the version using JOIN

Upvotes: 0

Views: 117

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269963

This might be easier to understand if you replace the max() with a sum().

Consider this select statement:

SELECT s.sno, s.sname,s.age, SUM(CASE WHEN t.cno = 'CS112' THEN 1 ELSE 0 END) as NumCS112

The new column, NumCS112 has the number of times a student has taken the course. Next, put this in the having clause:

HAVING NumCS112 = 0

Well, this means that the number of times a student has taken the course is 0 -- so the student has not taken the course.

You can do the same thing with max(), where you get a flag instead of a count. So:

SELECT s.sno, s.sname,s.age, MAX(CASE WHEN t.cno = 'CS112' THEN 1 ELSE 0 END) as HasTaken_CS112
. . .
HAVING HasTaken_CS112 = 0

However, you don't have the expression in the select clause, so you can't use HasTaken_CS112. Instead, you have to use the full expression.

Upvotes: 1

Tom Cannaerts
Tom Cannaerts

Reputation: 648

You could add the cno filter in the JOIN condition instead of the where, and the look for NULL values in the RIGHT part of the join (indicating a non-match).

SELECT s.sno, s.sname, s.age
FROM students s LEFT JOIN take t
ON s.sno = t.sno AND t.cno = 'CS112'
WHERE t.cno IS NULL

Since the filter on cno is in the JOIN condition, it will not actually filter out the students (as opposed to a filter in the WHERE clause). In case of a non-match, the Take part of the join will contains NULL, something we then filter on (filtering out the cases where the student did indeed take the course).

Upvotes: 0

Adrian
Adrian

Reputation: 46452

The CASE inside the MAX yields 1 if they have the course, 0 if they don't. Taking the MAX of that CASE for each row, yields 1 if the student has any row where it would be 1 (meaning they have the class), and 0 otherwise. By saying they must HAVING the MAX > 1, you're saying they have to have the class. In the most convoluted way possible.

Upvotes: 1

Lawson
Lawson

Reputation: 634

The MAX ensures that if only one of the classes the student is taking is CS112, then it fails the HAVING statement and won't return that student.

Upvotes: 0

Related Questions