asked on

Error from union query

I am trying to list all schedules and then if there are services list those also, but I can't seem to get it to work.

I get the error...The query results cannot be enumerated more than once.

What is wrong with my Union?

Thanks

SELECT ISNULL(dbo.Schedules.ServiceID, 0) AS ServiceID, dbo.Schedules.SchedStartDateTime, dbo.Schedules.ServiceText, dbo.Schedules.CustomerID, 
                      ISNULL(dbo.Schedules.Completed, 0) AS Completed, dbo.Stylists.FullName, dbo.Stylists.StylistID, 'No Title' AS BeforePicName, 
                      '../images2020/resume-photo.jpg' AS BeforePicPath, 'No Title' AS AfterPicName, '../images2020/resume-photo.jpg' AS AfterPicPath
FROM         dbo.Schedules INNER JOIN
                      dbo.Stylists ON dbo.Schedules.UserID = dbo.Stylists.UserID

Where dbo.Schedules.CustomerID = 27
UNION 

SELECT ISNULL(dbo.Services.ServiceID, 0) AS ServiceID, dbo.Schedules.SchedStartDateTime, dbo.Schedules.ServiceText, dbo.Schedules.CustomerID, 
                      ISNULL(dbo.Services.Completed, 0) AS Completed, dbo.Stylists.FullName, dbo.Stylists.StylistID, ISNULL(dbo.Services.BeforePicName, 'No Title') AS BeforePicName, 
                      ISNULL(dbo.Services.BeforePicPath, '../images2020/resume-photo.jpg') AS BeforePicPath, ISNULL(dbo.Services.AfterPicName, 'No Title') AS AfterPicName, 
                      ISNULL(dbo.Services.AfterPicPath, '../images2020/resume-photo.jpg') AS AfterPicPath
FROM         dbo.Schedules INNER JOIN
                      dbo.Stylists ON dbo.Schedules.UserID = dbo.Stylists.UserID INNER JOIN
                      dbo.Services ON dbo.Schedules.ServiceID = dbo.Services.ServiceID

Where dbo.Schedules.CustomerID = 27
ORDER BY dbo.Schedules.SchedStartDateTime DESC

Open in new window