Re: subquery/alias question

Gregory Stark wrote:
> "Madison Kelly" <linux(at)alteeve(dot)com> 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