[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2008-11-11
6
Medium Priority
?
233 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:sqlagent007
  • 2
  • 2
  • 2
6 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 22933979
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
 
LVL 1

Author Comment

by:sqlagent007
ID: 22934012
I added a non clustered index to all 3 tables for FUND...
0
 
LVL 9

Expert Comment

by:Ernariash
ID: 22934048
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LVL 26

Expert Comment

by:tigin44
ID: 22934066
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
 
LVL 1

Author Comment

by:sqlagent007
ID: 22939499
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
 
LVL 9

Accepted Solution

by:
Ernariash earned 2000 total points
ID: 22940208

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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Loops Section Overview
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question