Postgres Pro
Downloads
Home   >   mailing lists

Thread: Howto retrieve all the grandchildren of all the children

Howto retrieve all the grandchildren of all the children

From
Joost Kraaijeveld
Date:
I have three tables. Table 1 contains the root of a tree. Table 2 contains
the children of table 1 (column acting as foreign key). Table 3 contains the
children of table 2 (column acting as foreign key). Basically it contains a
three level n-ary tree.

Is it possible to retrieve the grandchildren if I have the primary key of a
record in table 1, assuming that each table has a primary key and an
appropriate foreign key, and if so, what should be the SQL syntax to do so?

TIA

Joost Kraaijeveld

Re: Howto retrieve all the grandchildren of all the

From
"Jeffrey W. Baker"
Date:
On Fri, 2002-01-25 at 15:13, Joost Kraaijeveld wrote:
> I have three tables. Table 1 contains the root of a tree. Table 2 contains
> the children of table 1 (column acting as foreign key). Table 3 contains the
> children of table 2 (column acting as foreign key). Basically it contains a
> three level n-ary tree.
>
> Is it possible to retrieve the grandchildren if I have the primary key of a
> record in table 1, assuming that each table has a primary key and an
> appropriate foreign key, and if so, what should be the SQL syntax to do so?

SELECT table3.*
  FROM table3
     , table2
     , table1
 WHERE table3.parent = table2.id
   AND table2.parent = table1.id
   AND table1.id = 123456

-jwb

Re: Howto retrieve all the grandchildren of all the

From
"Jeffrey W. Baker"
Date:
On Fri, 2002-01-25 at 15:48, Jeffrey W. Baker wrote:
> On Fri, 2002-01-25 at 15:13, Joost Kraaijeveld wrote:
> > I have three tables. Table 1 contains the root of a tree. Table 2 contains
> > the children of table 1 (column acting as foreign key). Table 3 contains the
> > children of table 2 (column acting as foreign key). Basically it contains a
> > three level n-ary tree.
> >
> > Is it possible to retrieve the grandchildren if I have the primary key of a
> > record in table 1, assuming that each table has a primary key and an
> > appropriate foreign key, and if so, what should be the SQL syntax to do so?
>
> SELECT table3.*
>   FROM table3
>      , table2
>      , table1
>  WHERE table3.parent = table2.id
>    AND table2.parent = table1.id
>    AND table1.id = 123456

Oops, gratuitous use of join:

 SELECT table3.*
   FROM table3
      , table2
  WHERE table3.parent = table2.id
    AND table2.parent = 123456

-jwb