Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Unanswered: Brain working about as well as my outer join

    I went to a midnight showing of Godzilla last night which is not having a wonderful effect on my coding today. I am trying to do a very simple outer join and for some reason, it's not working as I expect.

    Code:
    SELECT a.*, isnull(b.units,0) AS [2013 Units]
        FROM AnalystTempDB.dbo.LiYang4 a
        LEFT OUTER JOIN AnalystTempDB.dbo.LiYang7 b
          ON a.shortsku = b.shortsku
        WHERE b.fiscalyr = 2013

    Table A has 29 unique ShortSKUs. When I outer join to Table B, I expect to return 29 records, 0's where B doesn't have a shortsku that is in A. But it is only returning 16 records, same as an inner join. What am I missing?


    EDIT: If I use a CTE, it works! Why??
    Code:
       ;WITH cte AS(
          SELECT * FROM AnalystTempDB.dbo.LiYang7
          WHERE fiscalyr = 2013)
        
        SELECT a.*,isnull(cte.units,0) AS [2013 units]
        FROM AnalystTempDB.dbo.LiYang4 a
        left outer JOIN cte ON a.shortsku = cte.shortsku
    Last edited by clawlan; 05-16-14 at 13:12.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just to humor an old phart, try removing the WHERE clause from your original SELECT statement with the LEFT OUTER JOIN that didn't produce the results that you wanted. Note the value in the result set for b.fiscalyr. Ooops that wasn't what you expected, was it?

    The CTE works because it does the filter operation before the JOIN operation, so it behaves more like what you expect (although it probably isn't exactly what you expect either). Moving the filter from the WHERE clause into the ON clause would achieve the same result.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Adding a filter to a left outer join converts it to an inner join.

    Try this instead:

    Code:
    SELECT a.*, isnull(b.units,0) AS [2013 Units]
    FROM AnalystTempDB.dbo.LiYang4 a
    LEFT OUTER JOIN AnalystTempDB.dbo.LiYang7 b
    ON a.shortsku = b.shortsku
        and b.fiscalyr = 2013
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    thanks both of you. that makes sense.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •