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

Trying to understand your request:
Does your table customerGroup define a recursive structure which shall be outlined?

Stephan

Abhisek schrieb:

> 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 Mon Jun 28 2004 - 11:43:03 CDT