y, March 31, 2011 3:20 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Counting records in a child table
Thanks. How would I do it with a window function? I thought windows only
compared groups of records in the same table.
--
Sent via pgsql-general mailing list (pgsql-general@post
Thanks. How would I do it with a window function? I thought windows
only compared groups of records in the same table.
On Thu, Mar 31, 2011 at 12:01 PM, David Johnston wrote:
> An alternative:
>
> SELECT
> parent.*,
> COALESCE(child.childcount, 0) AS whatever
> FROM parent
> LEFT JOIN
> (SELECT
An alternative:
SELECT
parent.*,
COALESCE(child.childcount, 0) AS whatever
FROM parent
LEFT JOIN
(SELECT parentid, count(*) as childcount FROM child GROUP BY parented) child
ON (parent.id = child.parentid)
You could also do:
SELECT parent.*,
COALESCE((SELECT count(*) FROM child WHERE child.id =
