Message-ID: <BLU0-SMTP965F0BF4A84C81E4C7400BAC500@phx.gbl>
Date: Fri, 8 Oct 2010 19:09:56 -0400
From: Frank Bax <fbax@sympatico.ca>
User-Agent: Thunderbird 2.0.0.23 (X11/20100309)
MIME-Version: 1.0
CC: pgsql-sql@postgresql.org
Subject: Re: counting related rows
References: <m3r5g0filx.fsf@carbon.jhcloos.org>
In-Reply-To: <m3r5g0filx.fsf@carbon.jhcloos.org>
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
Content-Transfer-Encoding: 7bit

James Cloos wrote:
> I have a table which includes a text column containing posix-style
> paths.  Ie, matching the regexp "^[^/]+(/[^/]+)*$".
> 
> I need to do a query of a number of columns from that table, plus the
> count of rows which are "children" of the current row.
> 
> The query:
> 
>   SELECT count(*) AS nch FROM m WHERE o = (SELECT o FROM m WHERE id=30016)
>                 AND name ILIKE (SELECT name || '/%' FROM m WHERE id=30016);
> 
> selects that extra column given the id.
> 
> A view containing all of the columns from m plus a column matching the
> above select would cover my needs well.
> 
> But I haven't been able to get the syntax right.


It would help if you provided:
a) statements to create sample data
b) expected results from sample data

Does this do what you want?

select * from m, (SELECT count(*) AS nch FROM m WHERE o = (SELECT o FROM 
m WHERE id=30016) AND name ILIKE (SELECT name || '/%' FROM m WHERE 
id=30016)) om;