Help required for Hierarchial Query (START WITH...CONNECT BY PRIOR)

Hi,

  I am facing a problem in righting a query. I am not able to get the desired results that I want. Following is the description of the problem:

I have two tables:

CREATE TABLE Customer(CustomerId NUMBER(10), CustomerGroupId NUMBER(10))
Here in this table customerId is the primary key and CustomerGroupId is the foriegn key to table customer group.

CREATE TABLE CustomerGroup(CustomerGroupId NUMBER(10), parentGroupId NUMBER(10))
Here customerGroupId is the primary key.

Each customer can either belong to a customerGroup or can exists alone. If it exists alone than the customerGroupId is 0.

Each customerGroup has its parentGroupId as 0 or have another customer group as its parent.

Consider the following scenario:
SQL> select customerId,customerGroupId from customer;

CUSTOMERID CUSTOMERGROUPID
---------- ---------------

         1               0
         2               0
         5               4
         6               4

SQL> select customerGroupId,parentGroupId from customerGroup;

CUSTOMERGROUPID PARENTGROUPID
--------------- -------------

              3             0
              4             3

In the result set I need each customerId and the complete heirarchy of the customerGroupId upto its parent 0.

Example
CustomerId CustomerGroupId

----------    ----------------
1                      0 (or NULL)
2                      0 (or NULL)
5                      4
5 or NULL              3
6                      4
6 or NULL              3  

I have tried to write the query with the help of startwith...connect by prior clause but not able achiveve the result set that I want.

Please help me out in building the query.

With Best Regards
Abhishek Received on Sat Jun 26 2004 - 03:05:16 CDT