From: | Frank Bax <fbax(at)sympatico(dot)ca> |
---|---|
To: | |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: counting related rows |
Date: | 2010-10-08 23:09:56 |
Message-ID: | BLU0-SMTP965F0BF4A84C81E4C7400BAC500@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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;
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Sargent | 2010-10-08 23:47:57 | Re: Duplicates Processing |
Previous Message | Gary Chambers | 2010-10-08 22:12:39 | Re: Duplicates Processing |