Need help on oracle sql query

Hi ,

I have an table called customer which  is in customers schema and has the below table structure.
Customers
Customer_id Number,
Customer_name varchar2(50)
customer_type number  -->always null value


I have another 2 tables in sales schema.

In this schema , i have an table called customer_sales , which has the below table structure

customer_sales
customer_id number --> ID from customer table in customers schema
Customer_name varchar2(50),
customer_type number --> which can be either 1,2,3 1--> internal customer , 2-external customer and 3 -both

Another table called Customer_profile in sales schema :
Customer_id number -> ID from customer table in customer schema.
customer_type number --> which can be either 1,2,3 1--> internal customer , 2-external customer and 3 -both


As part of the application functionality , when we create an customer from his site  , we insert record in customer table in customer schema and customer_profile in sales schema.
When we create an customer in sales person site , we insert an record in customer schema's customer table and sales schema's customer_sales.

The data is as below:
Customer:
CustomerId  Customer_name  customer_type
101                     xyz                        NULL
102                     abc                        NULL
103                      fgh                        NULL
104                      asd                        NULL
105                      wer                       NULL
106                      456                        NULL


Customer_sales
CustomerId  Customer_name  customer_type
101                  xyz                                 3
102                 abc                                  1


Customer_profile
customer_id customer_type
 103                    2

The requirenent is I need to select all customers who do not have an entry in customer_sales and customer_profile  . if it is present , then i should pull the data from either customer_sales or customer_profile . The output should be as below

Customer_id customer_name customer_type

101                     xyz                        3
102                     abc                        1
103                      fgh                        2
104                      asd                        NULL
105                      wer                       NULL
106                      456                        NULL


I should override the customer_type information for customer_type column in customer table if an entry is present in customer_sales or customer_profile table.

Any help is really appreciated.
sam_2012Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
See if this is what you want:
select c.customerid,
	coalesce(cs.customer_name, c.customer_name) customer_name,
	coalesce(cp.customer_type, cs.customer_type, c.customer_type) customer_type
from customer c
	left outer join customer_sales cs on c.customerid=cs.customerid
	left outer join customer_profile cp on c.customerid=cp.customer_id
order by 1
/

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Commented:
Not sure if coalesce is needed for customer_name if customer_type is always null in customers table. Wouldn't the following also work?
select c.customerid, c.customer_name, coalesce(cp.customer_type, cs.customer_type) customer_type
from customer c
left outer join customer_sales cs on c.customerid=cs.customerid
left outer join customer_profile cp on c.customerid=cp.customer_id
order by 1
0
sam_2012Author Commented:
Thanks for the quick response , but the requiremen as changed . Iam not so confident in writing the query for the below requirement , any help is really appreciated. Its more challenging query.

Today , the application team made an major change in the application functionality , Now we create an customer from his site , we insert an record in customer table . Now the customer type column is removed and would be derived from customer_type table for each sales person site. For example customer_id=107 record.
When we create an customer from sales person site , initially , if the customer  has not made the payment , we just create his details  and do not attach any customer type to him. In this case we insert an record in both customer and customer_sales . Now we have included an new column called product_group_id , which indicates the sales person site who created this customer, but we do not store the product_group_id in customer table. Also , when an sales person is moved between regions , it is possible that we insert one record for the customer in customer table and add his customer type in customer_type of the sales person schema. For example : customerid=110.

For example:

Customer:
 CustomerId  Customer_name  
 101                     xyz                    
 102                     abc                    
 103                      fgh                    
 104                      asd                  
 105                      wer                  
 106                      456                    
 107                      ssd        
109                       jkl
110                       yui

Customer_sales
 CustomerId  Customer_name  product_group_id
101                  xyz-modified                               405
102                 abc-modified                              678
109                 jkl                                                 978
110               yui-modified                                 678

 Customer_profile
 customer_id customer_type product_group_id
  103                    2                        978
  110                    3                        678
  101                   1                        405
  107                    1                        678.


Now my requirement is when i login in the applicaiton from sales person : 678 , i need to display all customer data from customer table , if any record is present for the customer id in customer_sales table then show the data from it and not from customer table. Also , the customer type will be derived as NULL for all the records in customer and customer sales table if no matching records are found for the combination customer_id and product _group_id . Now the ouput should be as below for product_group_id=678


Customer_id customer_name customer_type

101                     xyz -modified              NA --> because no record exists in customer_type table for the combination  
                                                                                    of customer id and product_group_id
 102                     abc                        NA -->        here we are overriding the customer info in customer table with  
                                                                             customer_sales table if we find an row for product_group_id =678 in
                                                                             customer sales table.
 103                      fgh                         NA          --> same reason as customer id 101
 104                      asd                        NA          --> same reason as customer id 101
 105                      wer                       NA      --> same reson as customer id 101
 106                      456                        NA      --.  same reason as customer 101
 107                      ssd                           1             --> in this case the sales person has moved from one region to another ,
                                                                                      hence his previous existing customer payment is recorded and his
                                                                                     type is inserted in the customer_type table. But there will no entry in
                                                                                       the customer_sales for this customer.
109                         jkl                            NA           --> Same reason as customer id 101
110                    yui-modified             3                --> Need to override the customer name in customer table, pick it from
                                                                                        customer sales table , it is present for the product group id 678 and
                                                                                         also pick the customer type table if an row is present for product
                                                                                        group id 678.
0
sam_2012Author Commented:
awesome
0
slightwv (䄆 Netminder) Commented:
>>Not sure if coalesce is needed for customer_name if customer_type is always null in customers table. Wouldn't the following also work?

The reason I added to coalesce was because of the requirement:
should override the customer_type information for customer_type column

I took that to mean that if a value exists in the other table, use it over the one in the customer table.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.