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?
 
ErnariashConnect With a Mentor Commented:

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
 
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.