From: | Gregory Stark <stark(at)enterprisedb(dot)com> |
---|---|
To: | "Madison Kelly" <linux(at)alteeve(dot)com> |
Cc: | "Michael Glaesemann" <grzm(at)seespotcode(dot)net>, "Alvaro Herrera" <alvherre(at)commandprompt(dot)com>, "PostgreSQL General" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: subquery/alias question |
Date: | 2007-09-26 13:07:10 |
Message-ID: | 87fy11bmo1.fsf@oxford.xeocode.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"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.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
From | Date | Subject | |
---|---|---|---|
Next Message | Madison Kelly | 2007-09-26 13:09:47 | Solved! Was: (subquery/alias question) |
Previous Message | Stefan Schwarzer | 2007-09-26 13:01:18 | Compare Content in Multidimensional Array [PHP/SQL] |