We help IT Professionals succeed at work.
Get Started

SQL Query issues with a JOIN

James Murphy
James Murphy asked
on
129 Views
Last Modified: 2021-04-21
Hi,

I am trying to add in another join this is what I am attempting to add in:

"LEFT JOIN de_transaction ON de_rowid = tr_rowid_debtor"

but everytime I do (even without adding anything else to the select part of the clause), the query takes a minute longer, and the results of the below parts of the select clause are incorrect - but they are correct when i don't have the additional join in there.

",SUM(CASE WHEN co.co_type = '0' and co.co_completed = '1' and co.co_description != '@tvarEmail' and co.co_who != 'Update' THEN 1 END) letter_count
,SUM(CASE WHEN co.co_type = '0' and co.co_completed = '1' and co.co_description = '@tvarEmail' THEN 1 END) Email_count
,SUM(CASE WHEN co.co_type = '22' and co.co_completed = '1' THEN 1 END) SMS_Count
,SUM(CASE WHEN co.co_type = '1' and co.co_completed = '1' THEN 1 END) Phone_Count"

SELECT CASE WHEN cl_number = '1003' THEN 'ZQQML' 
WHEN cl_number = '1004' THEN 'ZQQML' 
ELSE 'Z1QML'
END as TCODE
,de_listed_date
,de_number	
,de_cl_account
,cl_number
,de_name
,de_owing
,de_status
,de_address
,de_address_1
,de_city
,de_state
,de_zip
,de_home_phone
,de_cell_phone
,de_work_phone
,de_other_phone
,de_email
,cl_alt_number
,cl_user_2
,de_status
,de_listed_date
,de_active
,SUM(CASE WHEN co.co_type = '0' and co.co_completed = '1' and co.co_description != '@tvarEmail' and co.co_who != 'Update' THEN 1 END) letter_count
,SUM(CASE WHEN co.co_type = '0' and co.co_completed = '1' and co.co_description = '@tvarEmail' THEN 1 END) Email_count
,SUM(CASE WHEN co.co_type = '22' and co.co_completed = '1' THEN 1 END) SMS_Count
,SUM(CASE WHEN co.co_type = '1' and co.co_completed = '1' THEN 1 END) Phone_Count
,sum(CASE WHEN co.co_type = '0' and co.co_completed = '1' and co.co_who != 'Update' THEN 1
WHEN co.co_type = '1' and co.co_completed = '1' THEN 1 
WHEN co.co_type = '22' and co.co_completed = '1' THEN 1 END) as Contact_attempt_date
,max(CASE WHEN co.co_type = '0' and co.co_completed = '1' and co.co_who != 'Update' THEN co.co_done_date
WHEN co.co_type = '1' and co.co_completed = '1' THEN co.co_done_date 
WHEN co.co_type = '22' and co.co_completed = '1' THEN co.co_done_date END) as Contact_attempt_date
,CASE WHEN cl_number = '1003' THEN 'Tier 1' 
WHEN cl_number = '1004' THEN 'Tier 2' 
ELSE null
END as TIER
,de_business
,de_business_address
,de_sin
,de_dl
,de_dob
,Max(CASE WHEN co.co_type = '2' THEN co.co_done_date END) Last_status_change
,Max(CASE WHEN co.co_type = '3' THEN co.co_description END) Prom_desc
,Max(CASE WHEN co.co_type = '3' THEN co.co_amount END) Prom_amt
,Max(CASE WHEN co.co_type = '3' THEN co.co_date END) Prom_dd
,Max(CASE WHEN co.co_type = '3' THEN co.co_cdate END) Prom_cd
,Max(CASE WHEN co.co_type = '3' and co.co_repeat = 0 THEN 'No Repeat'
WHEN co.co_type = '3' and co.co_repeat = 1 THEN 'DAILY'
WHEN co.co_type = '3' and co.co_repeat = 2 THEN 'Weekly'
WHEN co.co_type = '3' and co.co_repeat = 3 THEN 'Bi-weekly'
WHEN co.co_type = '3' and co.co_repeat = 4 THEN 'Monthly' END) frequency
,Max(CASE WHEN co.co_type = '3' THEN co.co_cby END) Collector
    FROM debtor     
    JOIN client ON cl_rowid = de_rowid_client
    LEFT JOIN contact co ON de_rowid = co.co_rowid_debtor
    WHERE cl_number between '1003' and '1004' and de_active = 'A'    
GROUP BY
cl_number
,de_listed_date
,de_number
,de_cl_account
,de_name
,de_owing
,de_status
,de_address
,de_address_1
,de_city
,de_state
,de_zip
,de_home_phone
,de_cell_phone
,de_work_phone
,de_other_phone
,de_email
,cl_alt_number
,cl_user_2
,de_collector
,de_salesman
,de_owned_by_debtor
,de_principal
,de_paid
,de_last_payment_date
,de_last_worked_date
,de_active
,de_poe
,de_business
,de_business_address
,de_sin
,de_dob
,de_dl
,de_zip
,de_state
,de_cell_phone
,de_home_phone
,de_email
,de_owing

Open in new window


Could anyone please assist?

many thanks!
Comment
Watch Question
EE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This problem has been solved!
Unlock 1 Answer and 7 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE