Re: subquery/alias question - Mailing list pgsql-general

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;

Michael Glaesemann
grzm seespotcode net