Query tuning

Posted on 2014-02-23
Medium Priority
Last Modified: 2014-02-26
Hi ,

I have the below requirement , I have 5 tables called as customer , persondet , userdet .

Customer can be account holder , Sales Person , internal customer.

persondet : contains the peronal det of the customer

appuser : contains the user details mapped for each customer

userdet : contains the user details of customer who is authorised to view the sales.

salesdet : contains the comments about the sales made to the customer.

Structure  of the tabes is a sbelow:

Customer_id ,

comment_id ,
account_holder_id,(accountholder customer id from customer table)
sales_person_id,(salesperson customer id from customer table)
internalcust_id , (internal customer id from customer table)
accountholder_contact_id, (account holder contact id from persondet table)
salesperson_contact_id, (salesperson contact id from persondet table)
internalcust_contact_id (internal customer contact id from persondet table)

user_id (user id (appuser_id frin appuser table)


appuser :

appuser.appuser_id is the foreign key in userdet table.

I need to fetch the details of each sales comment in salesdet table for each customer type i.e accountholder, salesperson, internal.  If it is internal , I need get the user name from userdet table else from persondet table .

the salesdet table contains the  account_holder_id,sales_person_id,internalcust_id,accountholder_contact_id,
, these column contain the values from customer and persondet. Below is the query that i have written to get the results , I have been asked not to use multiple outer joins .

SELECT com.comment,
      case when cust_internal.is_internal='T' Then
        (Select ud.fname from appuser au , userdet ud where au.customer_id=cust_internal.customer_id
        and au.user_id=ud.user_id)
      end  internalfirstname,
    case when cust_internal.is_internal='T' Then
        (Select ud.lname from appuser au , userdet ud where au.customer_id=cust_internal.customer_id
        and au.user_id=ud.user_id)
      end  internallastname,
  cust_salesperson.customer_name ,
    con_salesperson.first_name ,
    cust_accountholder.customer_name ,
    con_accountholder.first_name ,
  FROM salesdet com
  lEFT OUTER JOIN customer cust_internal
  ON(com.account_holder_id = cust_internal.Customer_id)
  lEFT OUTER JOIN customer cust_salesperson
  ON(com.sales_person_id = cust_salesperson.Customer_id)
  lEFT OUTER JOIN customer cust_accountholder
  ON(com.account_holder_id = cust_accountholder.Customer_id)
  LEFT OUTER JOIN salesdet con_internal
  ON(com.internalcust_contact_id = con_internal.contact_id)
  LEFT OUTER JOIN salesdet con_salesperson
  ON(com.salesperson_contact_id = con_salesperson.contact_id)
  LEFT OUTER JOIN salesdet  con_accountholder
  ON(com.accountholder_contact_id = con_accountholder.contact_id)
  I need to re - modify the logic for getting this data, any help in this query is really appreciated
Question by:sam_2012
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
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1200 total points
ID: 39880656
>, I have been asked not to use multiple outer joins

you can use INNER JOIN only if you can be sure that in the related table there will be matching record(s) always  ... if that is not the case (in a optional relationship), the outer join is requires.

so, who is asking you to avoid the outer joins?

Author Comment

ID: 39881454
our technical team has requested. I need to use this query for an view definition. Is it possible to achieve the same using inline queries?
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 1200 total points
ID: 39881831
>our technical team has requested
with some specific reason?
knowing that  LEFT JOIN often cannot be avoided?

anyhow, please check for each "LEFT JOIN", if the relation between the tables is "mandatory" or "optional".

if it's mandatory, you can safely change from LEFT JOIN to INNER JOIN, for example:
FROM salesdet com
  LEFT OUTER JOIN customer cust_internal
ON(com.account_holder_id = cust_internal.Customer_id) 

Open in new window

I think that every sales shall have a account holder/customer record, so I would think that for example there, you can safely change to INNER JOIN there. but YOU have to check that, by doing this query:
select com.* FROM salesdet com
  LEFT OUTER JOIN customer cust_internal
ON(com.account_holder_id = cust_internal.Customer_id)
WHERE  cust_internal.Customer_id IS NULL

Open in new window

if that query returns any records, the relation is NOT mandatory, hence you cannot safely change to inner join.

ON(com.account_holder_id = cust_internal.Customer_id)

Author Closing Comment

ID: 39889062

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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…

719 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