SQL Query issues with a JOIN

James Murphy
James Murphy used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
First of all: Use always table alias names. It's not clear, what column comes from which table.

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.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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.
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Author

Commented:
if I have accidently added a field multiple times to the group by clause would that cause issues?
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
No.

CREATE TABLE Table1
    ([abc] varchar(1), [num] int)
;
    
INSERT INTO Table1
    ([abc], [num])
VALUES
    ('a', 1),
    ('a', 2),
    ('a', 3),
    ('a', 4)
;

select abc, sum(num)
from table1
group by abc, abc, abc
;

Open in new window

That query works, and the result is correct.

Your problem stems from a many to one relationship so when joined you get more rows.
We cannot directly solve this for you as we have no data to work with.

Your best way forward, if still needing help, is to supply some sample data for each table (similar to my example above).  The sample does not have to be anything private and it doesn't need to cover every possible column, just a representation of the way the tables relate to each other and any one to many relationships are included in the sample.

Author

Commented:
thank you, I have been away sick but will come back to you soon. many thanks!

Author

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial