Recursive SQL

From: <radioaktiv_at_my-deja.com>
Date: Thu, 10 Jun 1999 12:56:09 GMT
Message-ID: <7jocl5$h4r$1@nnrp1.deja.com>

I have a (for me) difficult problem. I have some tables:

CREATE TABLE Person(

   Person_ID          number(10) PRIMARY KEY,
   Name               varchar2 (50));

CREATE TABLE Project (
   Project_ID         number(10) PRIMARY KEY,
   Related_to_Project number (10),
   Project_Name       varchar2 (50),

FOREIGN KEY (Related_to_Project) REFERENCES Project (Project_ID));

CREATE TABLE Project_Pers (

   Project_ID         number (10) NOT NULL,
   Person_ID          number (10) NOT NULL,

FOREIGN KEY (Project_ID) REFERENCES Project (Project_ID), FOREIGN KEY (Person_ID) REFERENCES Person(Person_ID));

Now I want to see all projects in which a person cooperates and the projects to which it is related to and the projects to which them are related to and so forth...
That means I want to go down the tree from one or more leafs to the root, but I don't know the starting points, I only have a given Person_ID.
Is this possible in Oracle's SQL? I know that there is a possibility in DB2 but Oracle?
I would be very thankful for answers.

Thanx in advance

Knut (Germany)

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Jun 10 1999 - 07:56:09 CDT