Doubt on query

On Sep 25, 2008, at 4:59 PM, x asasaxax wrote:

Hi everyone,

I have this table:

create table cat(
cod integer,
cod_super integer,
constraint cod_super_fk Foreign Key(cod_super) references cat(cod),
constraint cod_pk Primary Key(cod)
);

insert into cat values(0, 1);
insert into cat values(1, 0);
insert into cat values(2, 0);
insert into cat values(3, 2);
insert into cat values(4, 3);
insert into cat values(5, 4);
insert into cat values(6, 0);
insert into cat values(7, 0);

The Query i want to do is:
I want to know all the children´s and subchildrens.
Example 1: I want to know the children´s of 0 will return 1, 2, 3,
4, 5, 6, 7
Example 2: I want to know the children´s of 1 will return nothing
Example 3: I want to know the children´s of 2 will return 3, 4, 5

Did anyone knows how can i do this query?

Thanks a lot
:)

Read this, it might shine a light for a other method of doing this.

http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html

Ries