:::: MENU ::::

SQL LeftJoin Template

Select Name, [Disk_Space] from

(Select * From V_FullcollectionMembership where CollectionID = ‘ XXXX’ ) AllMachines

( XXXXXXX  ) DiskSpace

On AllMachines.Name=Diskspace.[Computer Name]

Example :—

select Name, [DISK_SPACE]
(select *
from v_FullCollectionMembership
where collectionID = ‘XXX00BB5’) allMachines
left join
v_R_System.Netbios_Name0 AS [Computer Name], v_R_System.User_Name0 as [ User name],
DisplayName AS [Baseline Item],
v_StateNames.StateName AS [Baseline Status],

case v_StateNames.StateName when ‘Compliant’ then ‘Yes’ when ‘Non-Compliant’ then ‘No’ when ‘Unknown’ then ‘Unknown’ when ‘Error’ then ‘Error’ ELSE v_StateNames.StateName END AS [DISK_SPACE]

FROM   dbo.v_CICurrentComplianceStatus INNER JOIN
dbo.v_CIAssignmentToCI ON v_CICurrentComplianceStatus.CI_ID = v_CIAssignmentToCI.CI_ID INNER JOIN
dbo.v_CIAssignment ON v_CIAssignmentToCI.AssignmentID = v_CIAssignment.AssignmentID INNER JOIN
dbo.v_LocalizedCIProperties ON v_CICurrentComplianceStatus.CI_ID = v_LocalizedCIProperties.CI_ID INNER JOIN
dbo.v_StateNames ON v_CICurrentComplianceStatus.ComplianceState = v_StateNames.StateID INNER JOIN
dbo.v_R_System ON v_CICurrentComplianceStatus.ResourceID = v_R_System.ResourceID INNER JOIN
dbo.v_FullCollectionMembership ON dbo.v_R_System.ResourceID = dbo.v_FullCollectionMembership.ResourceID
WHERE (v_StateNames.TopicType = 401) AND (v_CIAssignment.CollectionID = ‘XXX00B86’)
AND (dbo.v_FullCollectionMembership.CollectionID = ‘XXX00B86’)
and ( dbo.v_LocalizedCIProperties.DisplayName = ‘Machines With Low Disk Space (Less Than 10GB)’)
) DiskSpace
on allMachines.Name = DiskSpace.[Computer Name]

So, what do you think ?