samedi 31 octobre 2015

SQL Join Query refactoring

I am trying to Join 3 tables based on specific columns. data_customer(main Table), data_customer_Address(Cust_id as foreign key) and Data_customer_contacts (Cust_id as foreign key) I have successfully achieved the result from below query:

select cst.cust_companyName as 'Company Name', cnt.cntct_fname as 'Contact First Name', 
cnt.cntct_lName as 'Contact Last Name', cnt.cntct_mainPhone as 'Main Phn Number',
cnt.cntct_Mobile as 'Mobile Number', cst.cust_mainEmail as 'Main Email', cnt.cntct_email as 'Contact Email',
adr.addressLine1 as 'Adress line 1',adr.addressLine2 as 'Address Line 2',
adr.City as 'City', adr.State as 'State', adr.pinZip as 'Pin/Zip Code'
 from data_customer as cst,data_customer_Address as adr,data_customer_contacts as cnt where
  cst.[cust_id]='2015Q4'
 and adr.[cust_id] ='2015Q4'
and
cnt.[cust_id]='2015Q4'

However, Cust_id will be passed into query dynamically. If I won't pass cust_id on any of the single place, I will get a Cartesian product. I tried other ways but could not short the query.

Kindly suggests is there any way I can improve the query or improve the performance?

Note*: I am using Sqlite on Windows.

Aucun commentaire:

Enregistrer un commentaire