Re: counting related rows

>>>>> "FB" == Frank Bax <fbax@sympatico.ca> writes:

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

FB> Does this do what you want?

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

That is almost right, except that it uses id=30016's nch value for every
row in the result, rather than computing each row's own nch.

As an example:

create TABLE m ( id integer primary key, o integer, name text,                f1 integer, f2 integer, f3 integer);

insert into m values (1, 3, 'a', 0, 1, 1);
insert into m values (2, 3, 'a/short', 1, 0, 1);
insert into m values (3, 3, 'a/short/path', 1, 0, 0);
insert into m values (4, 4, 'nothing', 0, 0, 1);
insert into m values (5, 2, 'nothing', 0, 1, 0);
insert into m values (6, 2, 'nothing/of', 1, 0, 0);
insert into m values (7, 2, 'nothing/of/value', 0, 0, 0);

The select should result in something like:
id | o |       name       | f1 | f2 | f3 | nch
----+---+------------------+----+----+----+----- 1 | 3 | a                |  0 |  1 |  1 |  2 2 | 3 | a/short
| 1 |  0 |  1 |  1 3 | 3 | a/short/path     |  1 |  0 |  0 |  0 4 | 4 | nothing          |  0 |  0 |  1 |  0 5 | 2 |
nothing         |  0 |  1 |  0 |  2 6 | 2 | nothing/of       |  1 |  0 |  0 |  1 7 | 2 | nothing/of/value |  0 |  0 |
0|  0
 

since rows 2 and 3 are children of row 1, row 3 is also a child of
row 2, rows 6 and 7 are children of row 5 and row 7 is also a child
of row 6.

-JimC
-- 
James Cloos <cloos@jhcloos.com>         OpenPGP: 1024D/ED7DAEA6