• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 431
  • Last Modified:

need help on sql query

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
sam_2012
Asked:
sam_2012
  • 2
  • 2
  • 2
  • +1
3 Solutions
 
PortletPaulCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
Walter RitzelSenior Software EngineerCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Walter RitzelSenior Software EngineerCommented:
Thanks, Paul. Will keep that in mind.
0
 
sam_2012Author Commented:
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
 
PortletPaulCommented:
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
 
sam_2012Author Commented:
Query was not answered , but have made changes to the tables.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now