Solved

need help on sql query

Posted on 2014-03-23
8
392 Views
Last Modified: 2014-04-23
Hi ,

I have an requriement for writing the query to the below scenario , I have an 4 tables
namely ,

customer
customer_type
customer_comments
customer_contacts

Customer tablel contians details about customer like his ID , name
Customer_type contains details about the customer type , he may be internal, external orparticipation. this is identified by the fields
Cusomer_type_id unique key
customer_id  foreign key referencing customer .id column
is_internal (Y or N) depending on whether the customer is internal . If Internal Y else N
is_external (Y or N) if customer is external Y else N
is_participation (Y or N) if customer is participation Y or N

A customer can be internal , external or participation , or a combination of all the 3.



Customer comments contains  comments details regarding  each customer type.
Comment_id unique key
customer1_id
contact1_id
customer2_id
contact2_id
customer3_id
contact3_id

There is an order followed while inserting the customer1_id , customer2_id , customer_3_id . These 3 columns are used to idenfity the comments for each customer when his profile changes .

For eaxmple :
initially the customer is internal,  In customer_comments
table , the rows would be enered as follows
Comment_id  : 1
comments : 'asas'
customer1_id : 2
contact1_id  : 45
customer2_id : NULL
contact2_id : NULL
customer3_id : NULL
contact3_id : NULL

Now if the customer becomes internal and external then

Comment_id  : 1
comments : 'asas'
customer1_id : 2
contact1_id  : 45
customer2_id : 2
contact2_id : 45
customer3_id : NULL
contact3_id : NULL

Now if the customer becomes internal, external  and participation  then

Comment_id  : 1
comments : 'asas'
customer1_id : 2
contact1_id  : 45
customer2_id : 2
contact2_id : 45
customer3_id : 2
contact3_id : 45


Customer_contacts contains the contact details for each customer
Contact_id
first_name
last_name.


My requirement is , I need to display the customer_comments details with their customer details like
comment_id
comments
Internalcustomer_type
Internalcustomerfirstname
Internalcustomerlastname
Externalcustomer_type
Externalcustomerfirstname
Externalcustomerlastname
Participationcustomer_type
Participationcustomerfirstname
Participationcustomerlastname

Now the challenge is the customer_id can be populated in customer_comments table in any order  in the customer1_id , customer2_id and customer3_id columns as mentioned above for the customer_comemnts example.

Iam not so strong on sql queries ,Any help on writing this query is much appreciated.
0
Comment
Question by:sam_2012
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
I'm sorry but the question isn't clear to me.

Are you saying that the table "Customer comments" contains  comments details regarding  each customer type where certain fields are used like this:

customer1_id --<< always for internal customers
contact1_id  --<< always for internal customers

customer2_id  --<< always for external customers
contact2_id  --<< always for external customers

customer3_id  --<< always for participation customers
contact3_id  --<< always for participation customers

or, are you saying that this is not true
"Now the challenge is the customer_id can be populated in customer_comments table in any order"
so there is no logic that applies to use of these columns.
0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 166 total points
Comment Utility
Table: customer_comments looks like a problem.  That table's design does not conform to data normalization theory.  Whenever tables are designed for a SQL_based system that do not fit with data normalization theory, your SQL statements will be come much more complex.

Do you have the option of changing the design of that table?
0
 
LVL 15

Assisted Solution

by:Walter Ritzel
Walter Ritzel earned 167 total points
Comment Utility
Here is something that could help you: http://sqlfiddle.com/#!4/06e9a/1
I believe this is the query you want, in terms of structure.
SELECT c.id
, c.comment_text
, c_int.type as internal_type
, c_int.first_name as internal_first_name
, c_int.last_name as internal_last_name
, c_ext.type as external_type
, c_ext.first_name as external_first_name
, c_ext.last_name as external_last_name
, c_par.type as participation_type
, c_par.first_name as participation_first_name
, c_par.last_name as participation_last_name
FROM customer_comments c
LEFT JOIN (select 'Internal' as type, c1.* from customer c1 inner join customer_type t on c1.id = t.customer_id and t.is_internal = 'Y') c_int ON c.customer1_id = c_int.id
LEFT JOIN (select 'External' as type, c1.* from customer c1 inner join customer_type t on c1.id = t.customer_id and t.is_external = 'Y') c_ext ON c.customer2_id = c_ext.id
LEFT JOIN (select 'Participation' as type, c1.* from customer c1 inner join customer_type t on c1.id = t.customer_id and t.is_participation = 'Y') c_par ON c.customer3_id = c_par.id

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 15

Expert Comment

by:Walter Ritzel
Comment Utility
Thanks, Paul. Will keep that in mind.
0
 

Author Comment

by:sam_2012
Comment Utility
Hi Portet,

I apologize for my late comments.

I agree with your comment

or, are you saying that this is not true
"Now the challenge is the customer_id can be populated in customer_comments table in any order"
so there is no logic that applies to use of these columns.

But the above query posted by walter will not meet this reuirement.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 167 total points
Comment Utility
On the basis of what you have told us I don't think there will be a query that will magically make that data consistent when it isn't stored in a consistent manner.

I also have to agree with markgeer, the current design is flawed and should be normalized.
0
 

Author Closing Comment

by:sam_2012
Comment Utility
Query was not answered , but have made changes to the tables.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now