Query to compare two SMS collections

Collection1 with 1000 systems and Collection2 with 400. This query lists the systems that are  in collection1, but not in Collection2.


In the following query replace XYZ00001 with the collection ID of Collection2 and replace SMS00001 with collection ID of Collection1.

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 inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_R_System.ResourceId not in (select sys.resourceid from sms_cm_res_coll_XYZ00001 AS coll, sms_r_system as sys where sys.resourceid = coll.resourceid) and SMS_G_System_SYSTEM.ResourceID in (select sys.resourceid from sms_cm_res_coll_SMS00001 AS coll, sms_r_system as sys where sys.resourceid = coll.resourceid)