0

Have a SQL Query on which I want to join another query to.

First code:

SELECT [Name] AS ComputerName,
       [SiteCode],
       [ClientVersion],
       [LastHardwareScan],
       [LastMPServerName] AS ManagementPoint,
       [CNIsOnline] AS OnlineState,
       MAX(CNLastOfflineTime) [CNLastOfflineTime],
       MAX(CNLastOnlineTime) [CNLastOnlineTime],
       CollectionID,
       [Description],
       [HotFixID],
       InstallDate

       -- First table
FROM [CM_C08].[dbo].[v_CollectionMemberClientBaselineStatus]
    -- Join v_R_System table
    LEFT OUTER JOIN [v_R_System]
        ON ([v_CollectionMemberClientBaselineStatus].[Name] = [v_R_System].Name0)
    
    LEFT OUTER JOIN
    (

SELECT [ResourceID],
       MAX([Description0]) [Description],
       MAX([HotFixID0]) [HotFixID],
       MAX(CAST(InstalledOn0 AS DATE)) AS InstallDate
       -- Second table
FROM [CM_C08].[dbo].[v_GS_QUICK_FIX_ENGINEERING]

      GROUP BY [ResourceID]
    ) AS UpdateCompliance
        ON (UpdateCompliance.ResourceID = [v_R_System].ResourceID)
WHERE CollectionID = 'C08000CC'
      AND CNIsOnline IN ('1','0')      

GROUP BY Name,
         sitecode,
         Clientversion,
         LastHardwareScan,
         CNIsOnline,
         LastMPServerName,
         CollectionID,
         v_R_System.ResourceID,
         Description,
         HotFixID,
         InstallDate

ORDER BY InstallDate DESC;

And I want to JOIN this query to it and sync using ComputerName (which I have in both querys) is that possible?

SELECT  SW.Name as 'ServiceWindowName',
        SW.Description,
        FCM.Name AS 'ComputerName',
        C.Name AS 'CollectionName'

FROM [v_ServiceWindow] SW

JOIN CM_C08.dbo.v_FullCollectionMembership FCM ON FCM.CollectionID=SW.CollectionID
JOIN CM_C08.dbo.v_Collection C ON C.CollectionID=FCM.CollectionID

As soon as I try to merge that last query using LEFT INNER JOIN it complains that LEFT is wrong. I gues I'm placing it wrong in the query. Could someone give me some directions on how to proceed?

EDIT 1

SELECT [Name] AS ComputerName,
       [SiteCode],
       [ClientVersion],
       [LastHardwareScan],
       [LastMPServerName] AS ManagementPoint,
       [CNIsOnline] AS OnlineState,
       MAX(CNLastOfflineTime) [CNLastOfflineTime],
       MAX(CNLastOnlineTime) [CNLastOnlineTime],
       CollectionID,
       [Description],
       [HotFixID],
       InstallDate,

       -- First table
FROM [CM_C08].[dbo].[v_CollectionMemberClientBaselineStatus]
    -- Join v_R_System table
    LEFT OUTER JOIN [v_R_System]
        ON ([v_CollectionMemberClientBaselineStatus].[Name] = [v_R_System].Name0)

    LEFT INNER JOIN
    (
    SELECT  SW.Name as 'ServiceWindowName',
        SW.Description,
        FCM.Name AS 'ComputerName',
        C.Name AS 'CollectionName'

    FROM [v_ServiceWindow] SW
) ON ([v_CollectionMemberClientBaselineStatus].[Name] = [v_ServiceWindow].[Name]

JOIN CM_C08.dbo.v_FullCollectionMembership FCM ON FCM.CollectionID=SW.CollectionID
JOIN CM_C08.dbo.v_Collection C ON C.CollectionID=FCM.CollectionID

    LEFT OUTER JOIN
    (

SELECT [ResourceID],
       MAX([Description0]) [Description],
       MAX([HotFixID0]) [HotFixID],
       MAX(CAST(InstalledOn0 AS DATE)) AS InstallDate
       -- Second table
FROM [CM_C08].[dbo].[v_GS_QUICK_FIX_ENGINEERING]

      GROUP BY [ResourceID]
    ) AS UpdateCompliance
        ON (UpdateCompliance.ResourceID = [v_R_System].ResourceID)
WHERE CollectionID = 'C08000CC'
      AND CNIsOnline IN ('1','0')      

GROUP BY Name,
         sitecode,
         Clientversion,
         LastHardwareScan,
         CNIsOnline,
         LastMPServerName,
         CollectionID,
         v_R_System.ResourceID,
         Description,
         HotFixID,
         InstallDate,
         SW.Name

ORDER BY InstallDate DESC;

This gives error:

Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'FROM'.
Msg 156, Level 15, State 1, Line 28
Incorrect syntax near the keyword 'ON'.
Msg 156, Level 15, State 1, Line 44
Incorrect syntax near the keyword 'AS'.

Completion time: 2021-02-16T09:02:53.4781044+01:00

FINAL CODE

SELECT [Name] AS ComputerName,
       [SiteCode],
       [ClientVersion],
       [LastHardwareScan],
       [LastMPServerName] AS ManagementPoint,
       [CNIsOnline] AS OnlineState,
       MAX(CNLastOfflineTime) [CNLastOfflineTime],
       MAX(CNLastOnlineTime) [CNLastOnlineTime],
       CollectionID,
       [Description],
       [HotFixID],
       InstallDate,
       SWDescription

       -- First table
FROM [CM_C08].[dbo].[v_CollectionMemberClientBaselineStatus]
    -- Join v_R_System table
    LEFT OUTER JOIN [v_R_System]
        ON ([v_CollectionMemberClientBaselineStatus].[Name] = [v_R_System].Name0)

LEFT JOIN
(
SELECT  SW.Name as 'ServiceWindowName',
    SW.Description as 'SWDescription',
    FCM.Name AS 'ComputerNames',
    C.Name AS 'CollectionName'

FROM [v_ServiceWindow] SW
JOIN v_FullCollectionMembership FCM ON FCM.CollectionID=SW.CollectionID
JOIN v_Collection C ON C.CollectionID=FCM.CollectionID
) FullCollectionMembership ON ([v_CollectionMemberClientBaselineStatus].[Name] = FullCollectionMembership.ComputerNames) 

    LEFT OUTER JOIN
    (

SELECT [ResourceID],
       MAX([Description0]) [Description],
       MAX([HotFixID0]) [HotFixID],
       MAX(CAST(InstalledOn0 AS DATE)) AS InstallDate
       -- Second table
FROM [CM_C08].[dbo].[v_GS_QUICK_FIX_ENGINEERING]

      GROUP BY [ResourceID]
    ) AS UpdateCompliance
        ON (UpdateCompliance.ResourceID = [v_R_System].ResourceID)
WHERE CollectionID = 'C04000CC'
      AND CNIsOnline IN ('1','0')      

GROUP BY Name,
         sitecode,
         Clientversion,
         LastHardwareScan,
         CNIsOnline,
         LastMPServerName,
         CollectionID,
         v_R_System.ResourceID,
         Description,
         HotFixID,
         InstallDate,
         SWDescription

ORDER BY InstallDate DESC;
2
  • Either INNER or LEFT OUTER joins exists, LEFT INNER is impossible. Typo? Commented Feb 16, 2021 at 7:56
  • the subselect needs a table alias, as all tables inside the subselect are not visible outside the subselect Commented Feb 16, 2021 at 8:17

1 Answer 1

1
LEFT INNER JOIN
(
SELECT  SW.Name as 'ServiceWindowName',
    SW.Description,
    FCM.Name AS 'ComputerName',
    C.Name AS 'CollectionName'

FROM [v_ServiceWindow] SW
) ON ([v_CollectionMemberClientBaselineStatus].[Name] = [v_ServiceWindow].[Name]

should be changed to

LEFT JOIN
(
SELECT  SW.Name as 'ServiceWindowName',
    SW.Description,
    FCM.Name AS 'ComputerName',
    C.Name AS 'CollectionName'

FROM [v_ServiceWindow] SW
JOIN CM_C08.dbo.v_FullCollectionMembership FCM ON FCM.CollectionID=SW.CollectionID
JOIN CM_C08.dbo.v_Collection C ON C.CollectionID=FCM.CollectionID
) ServiceWindow ON ([v_CollectionMemberClientBaselineStatus].[Name] = ServiceWindow.ServiceWindowName)
Sign up to request clarification or add additional context in comments.

5 Comments

When I tried to change to your suggestion the selection I made (SW.Name,SW.DEscription etc.) failes with error: The multi-part identifier "SW.Name" could not be bound.
I have made edits to fix the syntax. Please check.
Better, no errors but no data either. Where should I place ServiceWindowName to get some data to the table? And isn't ServiceWindow ON ([v_CollectionMemberClientBaselineStatus].[Name] = ServiceWindow.ServiceWindowName) wrong? Shouldn't [v_CollectionMemberClientBaselineStatus].[Name] be compaired with FCM.Name AS 'ComputerName' somehow?
I did it wrong from the beginning. I need to compair ([v_CollectionMemberClientBaselineStatus].[Name] = FullCollectionMembership.ComputerName) with I changed to but now I don't know how to be able to display that data into the table.
Never mind I fixed it. I updated my code above in case someone else needs it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.