Neither. I'd use this one. resourceid is resourceid is resourceid. there
is no reason to join sms_r_system in the subselect itself. Extra work for
SQL, and not needed. We've done this type of 'not in' subselect lots of
times.
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_G_System_INSTALLED_SOFTWARE
where ARPDisplayName in
("Microsoft .NET Framework 4.5.2","Microsoft .NET Framework
4.6.1","Microsoft .NET Framework 4.6","Microsoft .NET Framework 4.6.2")
)
On Tue, Apr 4, 2017 at 6:32 AM, Garth Jones <[email protected]> wrote:
> Hi Henry, what exactly is the goal? Is it to find all computers without
> .net 4.5.2?
>
>
>
> *From:* [email protected] [mailto:listsadmin@lists.
> myitforum.com] *On Behalf Of *[email protected]
> *Sent:* April 4, 2017 6:16 AM
> *To:* [email protected]
> *Subject:* [mssms] SQL Query question
>
>
>
> Hello SQL Guru’s
>
>
>
> We’re having a discussion with some Admin’s in Europe and I would like an
> opinion on 2 Collection queries looking for the same result:
>
>
>
> Which one is more efficient?
>
>
>
> *Collection Query #1:*
>
> 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 SMS_R_System.ResourceId
>
> from SMS_R_System
>
> inner join SMS_G_System_INSTALLED_SOFTWARE on
> SMS_G_System_INSTALLED_SOFTWARE.ResourceID
> = SMS_R_System.ResourceId
>
> where SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName in
>
> ("Microsoft .NET Framework 4.5.2","Microsoft .NET Framework
> 4.6.1","Microsoft .NET Framework 4.6","Microsoft .NET Framework 4.6.2")
>
> )
>
> =================================================================
>
> *Collection Query #2:*
>
> 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 sms_r_system.resourceid FROM
> sms_r_system
>
> INNER JOIN sms_g_system_installed_software ON
> sms_g_system_installed_software.resourceid
> = sms_r_system.resourceid
>
> WHERE sms_g_system_installed_software.arpdisplayname = "microsoft .net
> framework 4.5.2"
>
> or sms_g_system_installed_software.arpdisplayname = "microsoft .net
> framework 4.6.1"
>
> or sms_g_system_installed_software.arpdisplayname = "microsoft .net
> framework 4.6"
>
> or sms_g_system_installed_software.arpdisplayname = "microsoft .net
> framework 4.6.2"
>
> ======================================================================
>
>
>
> A DCM has also been mentioned to achieve the same result.
>
>
>
> Comments or recommendations?
>
>
>
> Thanks
>
>
>
> *Best regards / Cordialement *
>
>
>
> *Henry E. WILSON*
>
> Technical Expert Engineer
>
> Information Technology & Solutions
> Infrastructure Management
>
> Automation Services
> 55 Corporate Drive
>
> Bridgewater, NJ 08807
>
> TEL.: +1 908 981 3288 <(908)%20981-3288>
> CELL.: +1 610 724 1169 <(610)%20724-1169>
>
> FAX: +1 908 635 5858 <(908)%20635-5858>
>
> System Center Technical Blog
> <http://wssamer.sanofi.com/ws/AutomationCenterProvTools/SCTechBlog/Lists/Posts/AllPosts.aspx>
> (for
> internal use only)
>
> SCCM 2012 Dashboard
> <http://xspw10w201w/Reports/Pages/Report.aspx?ItemPath=%2fUnRestricted+Reports%2fInfrastructure+Health+Status%2fConfigMgr+2012+Dashboard>
> (for
> internal use only)
>
> Provisioning Tools & Lifecycle Sharepoint Site
> <http://wssamer.sanofi.com/ws/AutomationCenterProvTools/Documents/Forms/AllItems.aspx>
> (for
> internal use only)
>
>
>
>
>
>
--
Thank you,
Sherry Kissinger
My Parameters: Standardize. Simplify. Automate
Blogs: http://www.mofmaster.com, http://mnscug.org/blogs/sherry-kissinger,
http://www.smguru.org
