Gregory Stark wrote:
"Madison Kelly" <[EMAIL PROTECTED]> 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
On Sep 26, 2007, at 7:41 , Madison Kelly wrote:
Unfortunately, in both cases I get the error:
Um, the two cases could not be giving the same error as they don't
both contain the syntax that the error is complaining about: the
first case uses count in a subquery so it couldn't throw thi
"Madison Kelly" <[EMAIL PROTECTED]> 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,
Madison Kelly wrote:
> Thanks for your reply!
>
> Unfortunately, in both cases I get the error:
>
> nmc=> SELECT dom_id, dom_name, COUNT(usr_dom_id) AS usr_count FROM domains
> JOIN users ON (usr_dom_id=dom_id) HAVING COUNT (usr_dom_id) > 0 ORDER BY
> dom_name;
> ERROR: syntax error at or nea
Michael Glaesemann wrote:
On Sep 25, 2007, at 17:30 , Alvaro Herrera wrote:
Michael Glaesemann wrote:
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 user
On Sep 25, 2007, at 21:44 , Tom Lane wrote:
... which in English means we just do the calculation once ...
As always, thanks, Tom, for the explanation (and Alvaro, who probably
already knew this :))
Michael Glaesemann
grzm seespotcode net
---(end of broadcast)--
Michael Glaesemann <[EMAIL PROTECTED]> writes:
> I believe you'd have to write it like
> select dom_id, dom_name, count(usr_dom_id) as usr_count
>from domains
>join users on (usr_dom_id = dom_id)
>having count(usr_dom_id) > 0
>order by dom_name;
> I don't know how the performance
On Sep 25, 2007, at 17:30 , Alvaro Herrera wrote:
Michael Glaesemann wrote:
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
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 W
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
10 matches
Mail list logo
