Gregory Stark wrote:
"Madison Kelly" <linux@xxxxxxxxxxx> writes:
SELECT d.dom_id, d.dom_name FROM domains d WHERE (SELECT COUNT(*) FROM users u
WHERE u.usr_dom_id=d.dom_id) > 0 ORDER BY d.dom_name ASC;
Which gives me just the domains with at least one user under them, but not
the count. This is not ideal, and I will have to come back to it next week. In
the meantime, any idea what the GROUP BY error is? If not, I'll read through
the docs on 'GROUP'ing once I get this deadline out of the way.
I think you just want simply:
SELECT dom_id, dom_name, count(*)
FROM users
JOIN domains ON (usr_dom_id=dom_id)
GROUP BY dom_id, dom_nmae
ORDER BY dom_name
You don't actually need the HAVING (though it wouldn't do any harm either)
since only domains which match a user will come out of the join anyways.
You can also write it using a subquery instead of a join
SELECT *
FROM (
SELECT dom_id, dom_name,
(SELECT count(*) FROM users WHERE user_dom_id = dom_id) as nusers
FROM domains
) as subq
WHERE nusers > 0
ORDER BY dom_name
But that will perform worse in many cases.
You are right, the 'HAVING' clause does seem to be redundant. I removed
it and ran several 'EXPLAIN ANALYZE's on it with and without the
'HAVING' clause and found no perceivable difference. I removed the
'HAVING' clause anyway, since I like to keep queries as minimal as possible.
Thank you!
Madi
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
