Outer Join

  • 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?

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

  • 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

  • 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)
    Intro to Tally Tables and Functions

  • 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?

  • Thanks to all.

    🙂

  • 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

  • Toni thanks for that I will give it a try.

  • 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

  • Thanks for the warning.

  • 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)

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

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