Solved

need help on sql query

Posted on 2014-03-23
8
413 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39949491
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 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 166 total points
ID: 39950207
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 16

Assisted Solution

by:Walter Ritzel
Walter Ritzel earned 167 total points
ID: 39952220
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 16

Expert Comment

by:Walter Ritzel
ID: 39953065
Thanks, Paul. Will keep that in mind.
0
 

Author Comment

by:sam_2012
ID: 39954798
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 49

Accepted Solution

by:
PortletPaul earned 167 total points
ID: 39954933
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
ID: 40017999
Query was not answered , but have made changes to the tables.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

705 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