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.