asked on

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.

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

awking00

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

Swaminathan K

ASKER

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.

Swaminathan K

ASKER

awesome

slightwv (䄆 Netminder)

>>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.