Re: subquery/alias question

"Madison Kelly" <linux@alteeve.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.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com