SQL Query issues with a JOIN
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"
Could anyone please assist?
many thanks!
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_owingCould anyone please assist?
many thanks!
When summing data be extremely careful with any joins as each join can multiply the the number of rows and lead to incorrect resukts. You may need to complete the summation as a subquery first, then join to other tables.
It really isnt possible to be precise as we know nothing about your data or tables.
It really isnt possible to be precise as we know nothing about your data or tables.
Without aliases on all the columns, it's impossible to help with this. In particular, we need to see how you are using any column(s) from the de_transaction table.
ASKER
if I have accidently added a field multiple times to the group by clause would that cause issues?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you, I have been away sick but will come back to you soon. many thanks!
ASKER
Thank you - you were dead on with the part about too many one to many rows and I created a few CTE's to get the job done.
thank you and apologies for how long it took for me to come back to you.
thank you and apologies for how long it took for me to come back to you.
Then: A join can always create more rows, when it's cardinality is not 1:1. So when you're sure, that the result is wrong, then your JOIN is incorrect. Either you've joined the wrong table or you missed a further predicate in the JOIN clause.