Issue with left join

  • mcfarlandparkway

    SSCertifiable

    Points: 7598

    SELECT DISTINCT DS.DepartmentId
    FROM dbo.DepartmentStore DS
    LEFT JOIN dbo.Student Std
    on Std.DS.DepartmentId = DS.DepartmentId
    and Std.EffDate = (select max(Std.EffDate)
    from dbo.Student Std1
    where Std1.DS.DepartmentId = Std.DepartmentId
    and Std1.EffDate <= Ds.DataCycle)
    WHERE DS.IsActive = 4

    i am expecting the results the records which are there in dbo.DepartmentStore that does'nt exists in dbo.Student.

    How i can tweak the above query? using left join.with the current above query i am getting all the records from left table with nonmatching .

  • ScottPletcher

    SSC Guru

    Points: 98174

    WHERE DS.IsActive = 4 AND Std.DepartmentId IS NULL

    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.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716284

    Scott has given you the hint. You join the two with a left join, and you get some rows from Department that are not in Student, and are filled with NULL For the Student side.

    Adding a filter in the WHERE clause (as Scott has done) will then remove these rows.

    If you add this in the ON clause, it won't work.

     

  • Chris Harshman

    SSC-Forever

    Points: 41820

    Your subquery in the WHERE clause will cause a problem, since Std.EffDate can be NULL if there is no record in the Student table for the DepartmentID.  It may also lead you to hitting the Student table twice when you should only need to hit it once depending on what indexes are available.  An OUTER APPLY may be a better approach.

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

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