Re: subquery/alias question

Michael Glaesemann wrote:
>
> On Sep 25, 2007, at 16:59 , Madison Kelly wrote:
>
>> SELECT
>> d.dom_id,
>> d.dom_name,
>> (SELECT COUNT(*) FROM users u WHERE u.usr_dom_id=d.dom_id)
>> AS
>> usr_count
>> FROM
>> domains d
>> WHERE
>> (SELECT COUNT(*) FROM users u WHERE u.usr_dom_id=d.dom_id) > 0
>> ORDER BY d.dom_name ASC;
>
> Why not just use a join? Something like this would work, I should think:
>
> select dom_id,
> dom_name,
> usr_count
> from domains
> natural join (select usr_dom_id as dom_id,
> count(usr_dom_id) as usr_count
> from users) u
> where usr_count > 0
> order by dom_name;

Maybe the usr_count should be tested in a HAVING clause instead of
WHERE? And put the count(*) in the result list instead of a subselect.
That feels more natural to me anyway.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.