Solved

Query tuning

Posted on 2014-02-23
4
214 Views
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:
Customer_id ,
Is_internal,
customer_name

salesdet:
comment_id ,
comments,
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)

userdet:
user_id (user id (appuser_id frin appuser table)
fname,
lname

persondet:
contact_id,
fname,
lname,

appuser :
customer_id,
appuser_id

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,
salesperson_contact_id,
internalcust_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 .

Query:
SELECT com.comment,
    cust_internal.customer_name,
      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)
    else
        con_internal.fname
      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)
    else
        con_internal.lname
      end  internallastname,
  cust_salesperson.customer_name ,
    con_salesperson.first_name ,
    con_salesperson.last_name,
    cust_accountholder.customer_name ,
    con_accountholder.first_name ,
    con_accountholder.last_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
0
Comment
Question by:sam_2012
  • 2
  • 2
4 Comments
 
LVL 142

Assisted Solution

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

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?
0
 

Author Comment

by:sam_2012
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?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 400 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)
0
 

Author Closing Comment

by:sam_2012
ID: 39889062
awesome
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

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…
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 shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

808 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