SQL query help

mpathare · #1 (**permalink**) 09-07-06, 13:22

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.

mpathare · #2 (**permalink**) 09-07-06, 13:56

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

LKBrwn_DBA · #3 (**permalink**) 09-07-06, 14:16

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>

Internet Services

Web Development

Digital Marketing

Consumer Tech