3 table join, how to pull only matching rows from a 3rd table based on the results of a 2 table join

I have a query that runs in seconds and returns 40974 rows. However now I need to join 1 more column from another table that very large. When I try to do this, it multiplies my results, when I add the distinct clause it causes my query to run for over 60 seconds and then I have to stop the query.

I need to add a column named REGLINE1 from PS_MASTER table, I would like to only pull the REGLINE1 where the RESULTS_OF_Q1.FUND = PS_MASTER.FUND. I can't figure out how to do this.

Everything works fine when I only have 2 tables I am performing inner join on. As soon as I add a 3rd, it multiplies results beyond 40974, then when I add distinct, it takes over 60 seconds.

here is an example of what works fine
FROM  
DM_SLS_TRANSACTION_HISTORY INNER JOIN
DM_SLS_FUND ON DM_SLS_TRANSACTION_HISTORY.FUND = DM_SLS_FUND.FUND

here is an example of what pukes:
FROM
DM_SLS_TRANSACTION_HISTORY INNER JOIN
DM_SLS_FUND ON DM_SLS_TRANSACTION_HISTORY.FUND = DM_SLS_FUND.FUND INNER JOIN
PS_MASTER ON DM_SLS_TRANSACTION_HISTORY.FUND = PS_MASTER.FUND
-- query that runs in sub seconds returns 40974 rows
SELECT  distinct 
DM_SLS_FUND.FUND_NAME,
--PS_MASTER.REGLINE1 AS INVESTOR_CONTACT,
DM_SLS_TRANSACTION_HISTORY.TRANSACTION_ID, 
DM_SLS_TRANSACTION_HISTORY.GROSS_AMOUNT_ORIG, 
DM_SLS_TRANSACTION_HISTORY.GROSS_AMOUNT, 
DM_SLS_TRANSACTION_HISTORY.TOTAL_SHARES, 
DM_SLS_TRANSACTION_HISTORY.TRADE_REP, 
DM_SLS_TRANSACTION_HISTORY.FUND, 
DM_SLS_TRANSACTION_HISTORY.POSTING_DATE, 
DM_SLS_TRANSACTION_HISTORY.TRADE_DATE
 
FROM   
 
DM_SLS_TRANSACTION_HISTORY INNER JOIN
DM_SLS_FUND ON DM_SLS_TRANSACTION_HISTORY.FUND = DM_SLS_FUND.FUND

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.

tigin44Commented:
since it takes to long to respond for the query when you added the table PS_MASTER to the join you should better check the column FUND and indexes on this column.
0
sqlagent007Author Commented:
I added a non clustered index to all 3 tables for FUND...
0
ErnariashCommented:
I would review the Indexes as tigin44 recommended, but for the multiplies results beyond 40974. You could use temp tables as sample here or subqueries. Thanks,...

SELECT FUND, REGLINE1 
INTO #PS_MASTER
FROM PS_MASTER 
--OR 
SELECT FUND, MAX(REGLINE1) REGLINE1
INTO #PS_MASTER
FROM PS_MASTER 
GROUP BY  FUND
-- query that runs in sub seconds returns 40974 rows
SELECT  distinct 
DM_SLS_FUND.FUND_NAME,
--PS_MASTER.REGLINE1 AS INVESTOR_CONTACT,
DM_SLS_TRANSACTION_HISTORY.TRANSACTION_ID, 
DM_SLS_TRANSACTION_HISTORY.GROSS_AMOUNT_ORIG, 
DM_SLS_TRANSACTION_HISTORY.GROSS_AMOUNT, 
DM_SLS_TRANSACTION_HISTORY.TOTAL_SHARES, 
DM_SLS_TRANSACTION_HISTORY.TRADE_REP, 
DM_SLS_TRANSACTION_HISTORY.FUND, 
DM_SLS_TRANSACTION_HISTORY.POSTING_DATE, 
DM_SLS_TRANSACTION_HISTORY.TRADE_DATE,
PS_MASTER.REGLINE1 
FROM
DM_SLS_TRANSACTION_HISTORY
 INNER JOIN DM_SLS_FUND ON DM_SLS_TRANSACTION_HISTORY.FUND = DM_SLS_FUND.FUND 
 INNER JOIN #PS_MASTER ON DM_SLS_TRANSACTION_HISTORY.FUND = PS_MASTER.FUND

Open in new window

0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

tigin44Commented:
so then change the join order of the tables.. since you are joining them with inner join try putting the table with minimum row to the first, the following second and the table having the maximum last...

select *
from table_minrow tm
          inner join table_middlerow to on tm.ID = to.ID
          inner join table_maxrow tl on to.ID = ts.ID
0
sqlagent007Author Commented:
I tried going from smallest to biggest, and as soon as I add the "DISTINCT" clause it will take over 60 seconds, then I kill it manually.

i will try the temp table option later today.
0
ErnariashCommented:

The engines in SQL from 2000 evolve intelligent enough to solve the order they perform the joins, you can see it on the execution plans.
Depending on your environment try the distinct or the group by. I am guessing you will need the group by to solve your problems.
SELECT Distinct  FUND, REGLINE1
INTO #PS_MASTER
FROM PS_MASTER
--OR
SELECT FUND, MAX(REGLINE1) REGLINE1
INTO #PS_MASTER
FROM PS_MASTER
GROUP BY  FUND

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
Microsoft SQL Server 2005

From novice to tech pro — start learning today.