This question may be one of the more challenging things to solve in SQL. And, the way to achieve the results may seem unorthodox.
OK, we want to get a list of all computers that do not have Winword.exe installed. Since, we are looking to create a query that executes fast, and can readily be used to join to other tables or views, we’ll just return the machineid (or resourceid in other tables).
Let’s start with the positive, how do we get a list of all computers that have winword.exe installed.
SELECT DISTINCT SMS_G_System_SYSTEM.MachineID
FROM System_DISC AS SMS_R_System
INNER JOIN System_DATA AS SMS_G_System_SYSTEM
ON SMS_G_System_SYSTEM.MachineID = SMS_R_System.ItemKey
INNER JOIN vSMS_G_System_SoftwareFile AS SMS_G_System_SoftwareFile
ON SMS_G_System_SoftwareFile.ClientId = SMS_R_System.ItemKey
WHERE SMS_G_System_SoftwareFile.FileName = 'WINWORD.EXE'
Will give us that list.
Now, to give us a list of all computers that do not have Winword.exe installed, we use a subselect query. A subselect is a query within a query, the results from the inner query are passed to the outer query.
Now, to answer the question posed earlier, how do we get a list of all computers that do not have winword.exe installed, the following query will give us that information. Check out the NOT IN clause (below), we are selecting the positive in the subquery, then excluding them from the main, or top-level query. This logic could also be used to return a list of computers where one type of software is installed, yet missing another.
SELECT DISTINCT SMS_G_System_SYSTEM.Name0
FROM System_DISC AS SMS_R_System
INNER JOIN System_DATA AS SMS_G_System_SYSTEM
ON SMS_G_System_SYSTEM.MachineID = SMS_R_System.ItemKey
where SMS_G_System_SYSTEM.MachineID
NOT IN
(SELECT DISTINCT SMS_G_System_SYSTEM.MachineID
FROM System_DISC AS SMS_R_System
INNER JOIN System_DATA AS SMS_G_System_SYSTEM
ON SMS_G_System_SYSTEM.MachineID = SMS_R_System.ItemKey
INNER JOIN vSMS_G_System_SoftwareFile AS SMS_G_System_SoftwareFile
ON SMS_G_System_SoftwareFile.ClientId = SMS_R_System.ItemKey
WHERE SMS_G_System_SoftwareFile.FileName = 'WINWORD.EXE')