CODEWITHSUNDEEP
mysqlgroup-byhaving
worenga
worenga

Reputation: 5856

MYSQL HAVING with nonexistent entries

i want to select a max of s2.maxcol or 0 if there are no entries. So far this works, but if there is no corresponding entry it is not returned:

SELECT MAX( s2.maxcol) AS max_col, s1 . *
FROM table AS s1
LEFT JOIN table AS s2 ON s2.parent = s1.id
GROUP BY s1.id
HAVING max_col <100

But i also want to have the rows where the left join returns no corresponding entry (so max(s2.maxcol) should be 0.

How can i solve that?

Upvotes: 0

Views: 102

Answers (2)

Mosty Mostacho
Mosty Mostacho

Reputation: 43474

I just gave this a quick look and have to leave right now. But maybe COALESCE might help. Here is the info

Maybe something like this? (UNTESTED!)

SELECT COALESCE(MAX(s2.maxcol), 0) AS max_col, s1 . *
FROM table AS s1
LEFT JOIN table AS s2 ON s2.parent = s1.id
GROUP BY s1.id
HAVING max_col <100

Hope that helps. Bye!

Upvotes: 1

worenga
worenga

Reputation: 5856

changed HAVING max_col <100 to HAVING max_col is NULL or max_col <100 which works flawlessly.

Upvotes: 0

Related Questions