November 8, 2019 at 9:43 pm
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 .
November 8, 2019 at 9:56 pm
WHERE DS.IsActive = 4 AND Std.DepartmentId IS NULL
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 8, 2019 at 9:59 pm
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.
November 8, 2019 at 10:13 pm
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply