December 12, 2023 at 2:58 pm
I have a table-value function that returns data for a report. However, it's not returning the correct data, so I've reworked it and it's now returning what I'd expect ... the thing is, to my obviously unseeing eyes the two queries should be functionally identical. Please can someone help?
This query doesn't work:
SELECT
Grades.[Name] AS Grade,
CASE WHEN Grades.[Rank] IS NULL THEN 0 ELSE Grades.[Rank] END AS GradeRank,
Specialties.[Name] AS Specialty
FROM
[Roster].[PublishedShifts] AS PShift
LEFT JOIN (
SELECT TOP 1
Specialties.[Name],
StaffSpecialties.StaffId
FROM
[Staff].[StaffSpecialties] AS StaffSpecialties INNER JOIN [Organisation].[Specialties] AS
Specialties ON StaffSpecialties.SpecialtyId = Specialties.Id
ORDER BY
StaffSpecialties.StartDate DESC) AS Specialties ON PShift.StaffId = Specialties.StaffId
LEFT JOIN (
SELECT TOP 1
Grades.[Name],
StaffGrades.StaffId,
Grades.[Rank]
FROM
[Staff].[StaffGrades] AS StaffGrades INNER JOIN [Staff].[Grades] AS Grades ON StaffGrades.GradeId
= Grades.Id
ORDER BY
StaffGrades.StartDate DESC) AS Grades ON PShift.StaffId = Grades.StaffId
This query works:
SELECT
(SELECT TOP 1
Grades.[Name] AS [GradeName]
FROM
[Staff].[StaffGrades] AS StaffGrades
INNER JOIN [Staff].[Grades] AS Grades ON StaffGrades.GradeId = Grades.Id
WHERE
(PShift.StaffId = StaffGrades.StaffId)
ORDER BY
StaffGrades.StartDate DESC) AS Grade,
(SELECT TOP 1
ISNULL(Grades.[Rank], 0) AS [Rank]
FROM
[Staff].[StaffGrades] AS StaffGrades
INNER JOIN [Staff].[Grades] AS Grades ON StaffGrades.GradeId = Grades.Id
WHERE
(PShift.StaffId = StaffGrades.StaffId)
ORDER BY
StaffGrades.StartDate DESC) AS [Rank],
(SELECT TOP 1
Specialties.[Name]
FROM
[Staff].[StaffSpecialties] AS StaffSpecialties INNER JOIN [Organisation].[Specialties] AS Specialties ON StaffSpecialties.SpecialtyId = Specialties.Id
WHERE
(PShift.StaffId = StaffSpecialties.StaffId)
ORDER BY
StaffSpecialties.StartDate DESC) AS Specialty
FROM
[Roster].[PublishedShifts] AS PShift
Thank you!
December 12, 2023 at 3:22 pm
In the one that doesn't work, I suspect it's because the TOP (1) is being applied before the join on StaffId.
If you were to push the StaffId into the subqueries to make them correlated, it might make them work as expected.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 12, 2023 at 4:46 pm
You are thinking LEFT JOIN works like OUTER APPLY (it doesn't). You can rewrite the first query using OUTER APPLY instead of LEFT JOIN:
SELECT Grades.[Name] AS Grade,
CASE WHEN Grades.[Rank] IS NULL THEN 0 ELSE Grades.[Rank] END AS GradeRank,
Specialties.[Name] AS Specialty
FROM [Roster].[PublishedShifts] AS PShift
OUTER APPLY (SELECT TOP 1
Specialties.[Name],
StaffSpecialties.StaffId
FROM [Staff].[StaffSpecialties] AS StaffSpecialties
INNER JOIN [Organisation].[Specialties] AS Specialties
ON StaffSpecialties.SpecialtyId = Specialties.Id
WHERE StaffSpecialties.StaffId = PShift.StaffId
ORDER BY StaffSpecialties.StartDate DESC) AS Specialties
OUTER APPLY (SELECT TOP 1
Grades.[Name],
StaffGrades.StaffId,
Grades.[Rank]
FROM [Staff].[StaffGrades] AS StaffGrades
INNER JOIN [Staff].[Grades] AS Grades
ON StaffGrades.GradeId = Grades.Id
WHERE StaffGrades.StaffId = PShift.StaffId
ORDER BY StaffGrades.StartDate DESC) AS Grades
;
December 13, 2023 at 8:44 am
Thanks to both of you. I have amended the query using OUTER APPLY (which I've only rarely used before) and that works fine.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply