How to exclude a collection from a collection in SCCM

To exclude a collection from another collection, add the query below to Criteria section by using Show Query Language window.

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId not in (select ResourceID from SMS_CM_RES_COLL_CAM001B6) and SMS_R_System.ResourceId not in (select ResourceID from SMS_CM_RES_COLL_SMS00011)

The bold blue text here is the excluded collection ID.

Another example

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,
SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,
SMS_R_SYSTEM.Client from SMS_R_System where
SMS_R_System.OperatingSystemNameandVersion = "Microsoft Windows NT Workstation 6.1"
and SMS_R_System.Client = 1 and SMS_R_System.ClientType = 1 and SMS_R_System.ResourceId
not in (select ResourceID from SMS_CM_RES_COLL_SMS00024)