5 table join returns way too many rows, DISTINCT not working either and DISTINCT takes way too long to run

The following query should not return more than 40000 rows, since the TRANSACTION_HISTORY table is only 40K rows. However when I let this run for more than 1 minute, i end up getting 1 million or so rows. When I try to use DISTINCT, the query just takes over 5 minutes then I manually kill it. I will be able to filter by date once I get everything loaded once, however I need to get all history loaded first. Here are the row counts for each table:

DM_SLS_TRANSACTION_HISTORY (count = 40000)
V_WHOLESALER (count = 16)
CONTACT_AGG (count = 544160
ACCOUNT_AGG (COUNT = 317)
DM_ACCOUNT_VALUE (COUNT = 45339)

Basically without a transaction the data is useless, so everything is based off the TRANSACTION_HISTORY table. Attached is what I have so far.
select 
 
DM_SLS_TRANSACTION_HISTORY.CONTACT_ID,
DM_SLS_TRANSACTION_HISTORY.FIRM_ID, 
DM_SLS_TRANSACTION_HISTORY.TRADE_DATE, 
DM_SLS_TRANSACTION_HISTORY.POSTING_DATE, 
DM_SLS_TRANSACTION_HISTORY.CUST_ACCT_NBR, 
DM_SLS_TRANSACTION_HISTORY.GROSS_AMOUNT, 
DM_SLS_TRANSACTION_HISTORY.TRADE_REP, 
DM_SLS_TRANSACTION_HISTORY.TRADE_REP_ORIG, 
DM_SLS_TRANSACTION_HISTORY.GROSS_AMOUNT_ORIG, 
V_WHOLESALER.FULL_NAME as WHOLESALER_NAME, 
CONTACT_AGG.LAST_NAME as REP_CONTACT_LNAME, 
CONTACT_AGG.FIRST_NAME as REP_CONTACT_FNAME, 
CONTACT_AGG.MIDDLE_NAME as REP_CONTAT_MNAME, 
ACCOUNT_AGG.NAME as ORIGINAL_BROKER, 
DM_SLS_TRANSACTION_HISTORY.FUND, 
DM_ACCOUNT_VALUE.REP_NAME AS ORIGINAL_REP
 
--into INVESTMENT_DETAIL_AGG
 
FROM         
DM_SLS_TRANSACTION_HISTORY , V_WHOLESALER, CONTACT_AGG, ACCOUNT_AGG, DM_ACCOUNT_VALUE
/*
INNER JOIN
V_WHOLESALER ON DM_SLS_TRANSACTION_HISTORY.TERR1 = V_WHOLESALER.TERR INNER JOIN
CONTACT_AGG ON DM_SLS_TRANSACTION_HISTORY.CONTACT_ID = CONTACT_AGG.CONTACT_ID INNER JOIN
ACCOUNT_AGG ON DM_SLS_TRANSACTION_HISTORY.FIRM_ID = ACCOUNT_AGG.FIRM_ID INNER JOIN
DM_ACCOUNT_VALUE ON DM_SLS_TRANSACTION_HISTORY.FIRM_ID = DM_ACCOUNT_VALUE.FIRM_ID
*/
where  
DM_SLS_TRANSACTION_HISTORY.fund = '3701' 
and DM_SLS_TRANSACTION_HISTORY.CONTACT_ID = CONTACT_AGG.CONTACT_ID 
and DM_SLS_TRANSACTION_HISTORY.FIRM_ID = ACCOUNT_AGG.FIRM_ID
and DM_SLS_TRANSACTION_HISTORY.FIRM_ID = DM_ACCOUNT_VALUE.FIRM_ID
and DM_SLS_TRANSACTION_HISTORY.TERR1 = V_WHOLESALER.TERR

Open in new window

LVL 1
sqlagent007Asked:
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.

SharathData EngineerCommented:
>> The following query should not return more than 40000 rows, since the TRANSACTION_HISTORY table is only 40K rows.

This is wrong. You may get more records when you join the tables.
DM_SLS_TRANSACTION_HISTORY (count = 40000)
CONTACT_AGG (count = 544160)

You are joining both these tables on Contact_ID. If you have contact_id repeated in CONTACT_AGG table you will have more records in your output.

For example, Table A has following 3 records.
EmpId
1      
2      
3      

Table B has below reocrds
DeptID      EMpId      
10      1
10      2
10      3
20      1

If you join both the tables on EMpID, how many record you will get? 4 records more than the records in Table A.
0
jfmadorCommented:
Hello you only use simple cross join in your query, you need to use inner join between every table in order to bet only 40000rows,

Cross join are join without any condition and link between two table, they will list every combinason of row on each side,

I don't know why but your join are defined in your query but commented

FROM        
DM_SLS_TRANSACTION_HISTORY INNER JOIN
V_WHOLESALER ON DM_SLS_TRANSACTION_HISTORY.TERR1 = V_WHOLESALER.TERR INNER JOIN
CONTACT_AGG ON DM_SLS_TRANSACTION_HISTORY.CONTACT_ID = CONTACT_AGG.CONTACT_ID INNER JOIN
ACCOUNT_AGG ON DM_SLS_TRANSACTION_HISTORY.FIRM_ID = ACCOUNT_AGG.FIRM_ID INNER JOIN
DM_ACCOUNT_VALUE ON DM_SLS_TRANSACTION_HISTORY.FIRM_ID = DM_ACCOUNT_VALUE.FIRM_ID

0

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