CODEWITHSUNDEEP
sqlplsqlsqlite
SOAMad
SOAMad

Reputation: 335

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.

Upvotes: 1

Views: 152

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271051

You need to learn proper join syntax. Then you won't have such problems with your query.

Simple rule: Never use commas in the FROM clause. Always use explicit JOIN. So, your QUERY should look like:

SELECT . . 
FROM data_customer cst JOIN
     data_customer_Address adr
     ON cst.cust_id = adr.cust_id JOIN
     data_customer_contacts cnt
     ON cst.cust_id = cnt.cust_id
WHERE cst.cust_id = '2015Q4';

Lo and behold. You will never get a Cartesian product by leaving out a parameter. And you only have to specify the customer id once.

Upvotes: 3

柯鴻儀
柯鴻儀

Reputation: 623

    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]=adr.[cust_id]  and cst.[cust_id]=cnt.[cust_id] 
    and cst.[cust_id]='2015Q4';

the sql parse usually will first fetch the record in the table cst through index(if it has) or full scan , then run two for ...loop for inner join the table adr and cnt.in both of those two for ... loop ,the table cst will be the "outer table".

Upvotes: 2

SOAMad
SOAMad

Reputation: 335

Never mind, I got this.

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]=adr.[cust_id]  and cst.[cust_id]=cnt.[cust_id]    
and 
cst.[cust_id]='2015Q4'

If you think there is another improved way to do it, please let me know.

Upvotes: 2

Related Questions