Left join not pulling all from left table

  • jeffshelix

    SSCrazy

    Points: 2242

    T has 57,000 distinct rows

    Z has over a million.  I did row over partition on the key field (Z.zpsh)

    When i run the following SQL, I only get 47,000 rows back.

    Shouldn't I get 57,000?   thanks

     

    select * from #temp1 T

    left join #temp2 Z

    on T.entry = Z.zpsh

    where z.rownumber = 1

  • Phil Parkin

    SSC Guru

    Points: 243853

    What do you mean by this?

    T has 57,000 distinct rows

    I mean, why did you use the word 'distinct'?

     

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • ZZartin

    SSC-Dedicated

    Points: 30390

    By including Z in the where clause you've effectively turned it into an inner join.

  • Michael L John

    One Orange Chip

    Points: 25797

    No, your WHERE clause is likely filtering the rows.

     

    Without the actual SQL statement, and some sample data, that's an educated guess.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • jeffshelix

    SSCrazy

    Points: 2242

    I used "distinct" to make it clear that I had no duplicate values.

     

     

  • ScottPletcher

    SSC Guru

    Points: 98214

    For an OUTER JOIN, you must put conditions on the possibly-missing table in the JOIN clause, not in the WHERE clause.

    select * from #temp1 T

    left join #temp2 Z

    on T.entry = Z.zpsh

    and z.rownumber = 1

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • jeffshelix

    SSCrazy

    Points: 2242

    That was it!!!  I actually printed your reply and pinned it to my cube!!!!!!!!!!

    Thanks so much

Viewing 7 posts - 1 through 7 (of 7 total)

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