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"

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!
James MurphyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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 AdvisorCommented:
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 DBACommented:
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

James MurphyAuthor Commented:
if I have accidently added a field multiple times to the group by clause would that cause issues?
PortletPaulEE Topic AdvisorCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
James MurphyAuthor Commented:
thank you, I have been away sick but will come back to you soon. many thanks!
James MurphyAuthor 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.