Outer Join

Question

Outer Join

Lyn Davies-370583

Old Hand

Points: 321
More actions
January 11, 2008 at 6:46 am

#70712

When attempting to select all records from one table and only those matching from a second, not all the records are being selected from the left outer join table. Has anyone any ideas please?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Could you post the code please

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lyn Davies-370583 Old Hand Points: 321 More actions · Answer 2

Please see code below:

SELECT TOP 100 PERCENT dbo.genpremloc.level_2, dbo.genpremloc.level_5, dbo.genpremloc.gen_code, dbo.genpremloc.app_budg,

dbo.genpremloc.adj_budg, dbo.genpremloc.rev_budg, dbo.vwCrystalEstPremisesReport.LineValue, dbo.vwCrystalEstPremisesReport.OrdDate

FROM dbo.genpremloc LEFT OUTER JOIN

dbo.vwCrystalEstPremisesReport ON dbo.genpremloc.gen_code = dbo.vwCrystalEstPremisesReport.gen_code

WHERE (dbo.genpremloc.level_5 LIKE '5075%') AND (dbo.vwCrystalEstPremisesReport.OrdDate >= CONVERT(DATETIME, '2006-08-01 00:00:00', 102)) AND

(dbo.vwCrystalEstPremisesReport.OrdDate <= CONVERT(DATETIME, '2007-07-31 00:00:00', 102))

ORDER BY dbo.genpremloc.level_5, dbo.genpremloc.level_2

toniupstny Hall of Fame Points: 3986 More actions · Answer 3

Without knowing more about the interrelationship of the fields and tables, my initial guess would be your WHERE clause is limiting the result set?

Toni

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 4

Exactly...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

This is one of those cases where your WHERE clause is turning your OUTER into an INNER....

You're only allowing for non-null "right" records (based on the WHERE clause, so it's not being treated as an outer join anymore....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Lyn Davies-370583 Old Hand Points: 321 More actions · Answer 6

Lyn Davies-370583

Old Hand

Points: 321

January 14, 2008 at 1:37 am

#767584

Thanks to all.

🙂

toniupstny Hall of Fame Points: 3986 More actions · Answer 7

There was an article in the newsletter about Derived Tables that offers a way to resolve your issue by taking the WHERE clause and creating a Derived Table for the subset of rows that meet the Where clause conditions then doing the JOIN on that subset. In that way you would get OUTER JOIN to return unmatched rows as you eliminated the filtering of the WHERE clause.

http://www.sqlservercentral.com/articles/DerivedTables/61388/

I am not totally sure but my understanding of the article would indicate the changed query would be similar to this (you will need to verify but the concept is there).

SELECT TOP 100 PERCENT dbo.genpremloc.level_2, dbo.genpremloc.level_5, dbo.genpremloc.gen_code, dbo.genpremloc.app_budg,

dbo.genpremloc.adj_budg, dbo.genpremloc.rev_budg, Derived.vwCrystalEstPremisesReport.LineValue, Derived.vwCrystalEstPremisesReport.OrdDate

FROM dbo.genpremloc LEFT OUTER JOIN

/* Derived table that covers the WHERE clause */

(SELECT dbo.genpremloc.level_5, dbo.vwCrystalEstPremisesReport.OrdDate,

dbo.vwCrystalEstPremisesReport.OrdDate

FROM dbo.genpremloc JOIN

dbo.vwCrystalEstPremisesReport ON dbo.genpremloc.gen_code = dbo.vwCrystalEstPremisesReport.gen_code

WHERE (dbo.genpremloc.level_5 LIKE '5075%') AND (dbo.vwCrystalEstPremisesReport.OrdDate >= CONVERT(DATETIME, '2006-08-01 00:00:00', 102)) AND

(dbo.vwCrystalEstPremisesReport.OrdDate <= CONVERT(DATETIME, '2007-07-31 00:00:00', 102))) as Derived

ON dbo.genpremloc.gen_code = Derived.vwCrystalEstPremisesReport.gen_code

ORDER BY dbo.genpremloc.level_5, dbo.genpremloc.level_2

Toni

Lyn Davies-370583 Old Hand Points: 321 More actions · Answer 8

Lyn Davies-370583

Old Hand

Points: 321

January 16, 2008 at 4:53 am

#768565

Toni thanks for that I will give it a try.

humbleDBA Hall of Fame Points: 3540 More actions · Answer 9

Hi Lyn,

With regards to Toni's link and Derived Tables, please read the discussion area too as there are some errors in what was said in the article. Not saying its not the right way, as it might be, but be aware of the discussion points and test, test, test... 🙂

HTH

Lyn Davies-370583 Old Hand Points: 321 More actions · Answer 10

Lyn Davies-370583

Old Hand

Points: 321

January 17, 2008 at 5:04 am

#769140

Thanks for the warning.

Ian Yates SSCoach Points: 19738 More actions · Answer 11

Why not just move the where clause conditions for the right-hand table into the join condition rather than creating the derived table? Same end result although perhaps not as easy to understand - the derived table may help you to break up the steps in your mind. Truth be told the query engine may well come up with an identical plan anyway (although you would be wise to check)