Results 1 to 3 of 3

Thread: SQL query help

  1. #1
    Join Date
    May 2006
    Posts
    6

    Unanswered: SQL query help

    I have 2 tables. Customer and customer_parent_hierarchy.

    customer table contains
    customer_key
    customer_number

    example customer
    100 00001
    101 00002
    102 10002

    customer_parent_hierarchy table contains
    customer_key
    parent_key

    example customer_parent_hierarchy
    100 102
    101 100
    102 102

    what I want the sql to do is output the following
    customer_key customer# parent_Key parent#
    100 00001 102 10002
    101 00002 100 00001
    102 10002 102 10002

    The query I've been working on dosent quite work
    Code:
    select p.parent_key, p.parent_number, c.customer_key, c.customer_number
    from
    (select cc.CUSTOMER_NUMBER as parent_number, ccph.PARENT_CUSTOMER_KEY as parent_key
    from CD_CUSTOMER cc, CD_CUSTOMER_parent_hierarchy ccph
    where
    ccph.HIERARCHY_TYPE = '-' and
    ccph.PARENT_CUSTOMER_KEY = cc.CUSTOMER_KEY) p, 
    
    (select cc.CUSTOMER_NUMBER as customer_number, ccph.CUSTOMER_KEY as customer_key
    from CD_CUSTOMER cc, CD_CUSTOMER_parent_hierarchy ccph
    where
    ccph.HIERARCHY_TYPE = '-' and
    ccph.CUSTOMER_KEY = cc.CUSTOMER_KEY) c
    Thanks for helping.

  2. #2
    Join Date
    May 2006
    Posts
    6
    I think I figured it out. This is what I did

    Code:
    select ccph.CUSTOMER_KEY, cc.CUSTOMER_NUMBER, ccp.CUSTOMER_NUMBER, ccph.PARENT_CUSTOMER_KEY 
    from CD_CUSTOMER cc,
    CD_CUSTOMER ccp,
    CD_CUSTOMER_parent_hierarchy ccph
    where
    ccph.HIERARCHY_TYPE = '-' and
    ccph.PARENT_CUSTOMER_KEY = ccp.CUSTOMER_KEY and
    ccph.CUSTOMER_KEY = cc.CUSTOMER_KEY

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Works for me:
    Code:
    SQL>Drop table cd_customer;
    
    Table dropped.
    
    SQL>Create table cd_customer
      2  (Customer_Key number(5), Customer_Number varchar2(7));
    
    Table created.
    
    SQL>insert into cd_customer values (100,'00001');
    
    1 row created.
    
    SQL>insert into cd_customer values (101,'00002');
    
    1 row created.
    
    SQL>insert into cd_customer values (102,'10002');
    
    1 row created.
    
    SQL>commit; 
    
    Commit complete.
    
    SQL>Drop table cd_customer_parent_hierarchy;
    
    Table dropped.
    
    SQL>create table cd_customer_parent_hierarchy
      2  (hierarchy_type char(1), customer_key number(5), parent_customer_key number
    (5));
    
    Table created.
    
    SQL>insert into cd_customer_parent_hierarchy values ('-', 100,102);
    
    1 row created.
    
    SQL>insert into cd_customer_parent_hierarchy values ('-', 101,100);
    
    1 row created.
    
    SQL>insert into cd_customer_parent_hierarchy values ('-', 102,102);
    
    1 row created.
    
    SQL>commit; 
    
    Commit complete.
    
    SQL>
    SQL>Select Ccph.Customer_Key, Cc.Customer_Number
      2       , Ccph.Parent_Customer_Key, Ccp.Customer_Number
      3    From Cd_Customer Cc
      4       , Cd_Customer Ccp
      5       , Cd_Customer_Parent_Hierarchy Ccph
      6   Where Ccph.Hierarchy_Type = '-'
      7     And Ccp.Customer_Key = Ccph.Parent_Customer_Key 
      8     And Cc.Customer_Key = Ccph.Customer_Key
      9   Order By 1;
    
    CUSTOMER_KEY CUSTOME PARENT_CUSTOMER_KEY CUSTOME
    ------------ ------- ------------------- -------
             100 00001                   102 10002
             101 00002                   100 00001
             102 10002                   102 10002
    
    SQL>


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •