0

I am needing to return the top 10 Schedules but one column, the HostName could return multiple values for that schedule. If I do a standard join with the Top 10, I get back in some cases, less than 10 schedules, but 10 overall records because a schedule can have more than one HostName related. I want the total records returned to not be limited, while ensuring that only 10 distinct ScheduleIds return.

Here is my flawed code, that works fine if the schedule only has a single HostName, but breaks if multiple.

SELECT TOP 10
            s.Id AS ScheduleId
        , s.GroupName
        , a.Id AS AppId
        , a.AppName
        , (SELECT HostName FROM PatchingTargets
            LEFT JOIN ScheduleTargets st on st.ScheduleId = s.Id
            WHERE st.ScheduleId = s.Id) AS HostName
FROM ScheduleTickets t
    LEFT JOIN Schedules s ON s.Id = t.ScheduleId
    LEFT JOIN Applications a ON a.Id = s.AppId
WHERE PatchSessionId = 19 AND CollectionId IS NUll
ORDER BY a.PatchingPriority, s.Idx

Here is the typical join, that always returns 10 rows but not all of the HostNames.

SELECT TOP 10
            s.Id AS ScheduleId
        , s.GroupName
        , a.Id AS AppId
        , a.AppName
        ,p.HostName
FROM ScheduleTickets t
    LEFT JOIN Schedules s ON s.Id = t.ScheduleId
    LEFT JOIN ScheduleTargets st on st.ScheduleId = s.Id
    LEFT JOIN dbo.PatchingTargets p on p.Id = st.PatchingTargetId
    LEFT JOIN Applications a ON a.Id = s.AppId
WHERE PatchSessionId = 19 AND CollectionId IS NUll
ORDER BY a.PatchingPriority, s.Idx

Results without HostNames, need to add: enter image description here

This is the bad result: enter image description here

And here is my working solution, maybe not elegant, but it seems to work:

SELECT    s.Id AS ScheduleId
        , s.GroupName
        , a.Id AS AppId
        , a.AppName
        ,p.HostName
FROM ScheduleTickets t
    LEFT JOIN Schedules s ON s.Id = t.ScheduleId
    LEFT JOIN ScheduleTargets st on st.ScheduleId = s.Id
    LEFT JOIN dbo.PatchingTargets p on p.Id = st.PatchingTargetId
    LEFT JOIN SchwabApplications a ON a.Id = s.AppId
WHERE PatchSessionId = 19 AND SCCMCollectionId IS NUll
    AND s.Id IN (
        SELECT TOP 10 s.Id AS ScheduleId 
        FROM ScheduleTickets t
            LEFT JOIN Schedules s ON s.Id = t.ScheduleId
            LEFT JOIN SchwabApplications a ON a.Id = s.AppId
        WHERE t.PatchSessionId = 19 AND t.SCCMCollectionId IS NUll
        ORDER BY a.PatchingPriority, s.Idx
    )
ORDER BY a.PatchingPriority, s.Idx
Connie DeCinko CS
  • 151
  • 1
  • 2
  • 14
  • 1
    Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ – Sean Lange Mar 16 '18 at 15:58
  • Possible duplicate of [How to concatenate text from multiple rows into a single text string in SQL server?](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) – Nik Shenoy Mar 16 '18 at 16:01
  • Confirm for me...you want the hostnames field to return a concatenated list of all host names for that ID? – Twelfth Mar 16 '18 at 16:02
  • No, not all HostNames in a single value, each must be on a separate result. The result could be dozens of rows, but there will always be 10 distinct ScheduleId values, no more, no less. – Connie DeCinko CS Mar 16 '18 at 16:07

1 Answers1

0

If you want up to 10 matching hostnames from the subquery, you can use outer apply:

SELECT s.Id AS ScheduleId, s.GroupName, a.Id AS AppId, a.AppName
       ps.HostName
FROM ScheduleTickets t LEFT JOIN
     Schedules s
     ON s.Id = t.ScheduleId LEFT JOIN
     Applications a
     ON a.Id = s.AppId OUTER APPLY
     (SELECT TOP (10) HostName
      FROM PatchingTargets JOIN
           ScheduleTargets st 
           ON st.ScheduleId = s.Id
      WHERE st.ScheduleId = s.Id
     ) ps
WHERE PatchSessionId = 19 AND CollectionId IS NUll
ORDER BY a.PatchingPriority, s.Idx
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786