Why don't these two queries return the same data?

  • 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!

    • This topic was modified 11 months ago by  edwardwill. Reason: Fixed missing formatting
  • 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.

    • This reply was modified 11 months ago by  Phil Parkin.

    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

  • 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
    ;

     

  • 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