Issue with left join

  • mcfarlandparkway


    Points: 7642

    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: 98434

    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: 719130

    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


    Points: 42081

    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