[ Home  |  FAQ-Related Q&As  |  General Q&As  |  Answered Questions ]

    Search the Q&A Archives

Hi, I am facing a problem in righting a query. I am not...

<< Back to general questions

Question by somu
Submitted on 6/26/2004
Related FAQ: N/A
Rating: Not yet rated Rate this question: Vote
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
Somu



Your answer will be published for anyone to see and rate.  Your answer will not be displayed immediately.  If you'd like to get expert points and benefit from positive ratings, please create a new account or login into an existing account below.


Your name or nickname:
If you'd like to create a new account or access your existing account, put in your password here:
Your answer:

FAQS.ORG reserves the right to edit your answer as to improve its clarity.  By submitting your answer you authorize FAQS.ORG to publish your answer on the WWW without any restrictions. You agree to hold harmless and indemnify FAQS.ORG against any claims, costs, or damages resulting from publishing your answer.

 

FAQS.ORG makes no guarantees as to the accuracy of the posts. Each post is the personal opinion of the poster. These posts are not intended to substitute for medical, tax, legal, investment, accounting, or other professional advice. FAQS.ORG does not endorse any opinion or any product or service mentioned mentioned in these posts.

 

<< Back to general questions

[ Home  |  FAQ-Related Q&As  |  General Q&As  |  Answered Questions ]
© 2008 FAQS.ORG. All rights reserved.