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;
INNERorLEFT OUTERjoins exists,LEFT INNERis impossible. Typo?