Subquery that results in an AND in the WHERE clause?-CodePudding

My company uses SCCM to manage our computers. Our field engineers use a particular Java-based GIS mapping application, and I need to write a SQL query to find out which computers have this application. Unfortunately, the application is "installed" on a computer by copying a bunch of files to a directory and placing a shortcut to a .JAR file on the desktop. There's no actual installer for this program, and therefore, SCCM does not have any way to detect that this application is present. To complicate matters, some people run it straight off the network; some from their desktop; and some from the root of their C: drive. There's no real consistency there for me to report against.

The good news is that the program requires Java, ArcGIS, Microsoft SQL Express, and SQL Management Studio to all be installed locally for the program to work. So if I can query for the presence of these programs, it's a very good bet that this computer is a field engineering PC. The bad news is that all four of these programs are also used by other things that have nothing to do with this field engineering app, and I don't want the query to include those.

So in short, the query must return a list of ONLY those computers that have ALL FOUR of these programs installed. It seems like a simple task but I'm kind of stuck on how to write it.

The naïve implementation below won't work because I need a subquery to look across multiple records, but all my attempts to do that wind up in a logical OR, which isn't want I want.

SELECT SMS_R_SYSTEM.Name
FROM SMS_R_System INNER JOIN SMS_G_System_ADD_REMOVE_PROGRAMS
    ON SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId
WHERE SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName LIKE "ArcGIS Engine%" AND
      SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName LIKE "Java 8%" AND
      SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName LIKE "Microsoft SQL Server%" AND
      SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "SQL Server Management Studio"

How do you get a logical AND here?

CodePudding user response：

Not the best way but this will be easy to debug for you. You can join to the same table multiple times, you just need to give it different aliases each time, each of these joins can be used to filter on a particular program:

SELECT S.Name
FROM SMS_R_System s
INNER JOIN SMS_G_System_ADD_REMOVE_PROGRAMS p1
    On p1.ResourceID = S.ResourceId
INNER JOIN SMS_G_System_ADD_REMOVE_PROGRAMS p2
    On p2.ResourceID = S.ResourceId
INNER JOIN SMS_G_System_ADD_REMOVE_PROGRAMS p3
    On p3.ResourceID = S.ResourceId
INNER JOIN SMS_G_System_ADD_REMOVE_PROGRAMS p4
    On p4.ResourceID = S.ResourceId
WHERE p1.DisplayName LIKE 'ArcGIS Engine%'
AND P2.DisplayName LIKE 'Java 8%'
AND p3.DisplayName LIKE 'Microsoft SQL Server%'
AND p4.DisplayName = 'SQL Server Management Studio'

CodePudding user response：

 SELECT SMS_R_SYSTEM.Name
    FROM SMS_R_System INNER JOIN SMS_G_System_ADD_REMOVE_PROGRAMS
        ON SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId
    WHERE SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName LIKE "ArcGIS Engine%" OR
          SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName LIKE "Java 8%" OR
          SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName LIKE "Microsoft SQL Server%" OR
          SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "SQL Server Management Studio" 
GROUP BY SMS_R_System.ResourceId 
HAVING COUNT(DISTINCT SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName) >= 4;

Here you

Select all resources and programs which match your criteria.
Then you group by resourse_id and count how many programs (which meet your criteria) were found per each resource_id.
Check that number. If the number of the distinct programs will be less than 4 - it is the case we are not interested in. If equal to 4 or even greater than 4 (in case your user has ArcGIS Engine v1 and ArcGIS Engine v2 isntalled, for example) - this is the case we are looking for.