-2

I would like to generate a query that lists all PCs with any version of Java except for 6. They query I have created shows PCs with any version of Java including 6, I want to list ONLY PCs that have Java BUT not 6.

Here is what I got...

SELECT 
    arp.DisplayName0 AS 'Product',
    arp.Version0 AS 'Version',
    sys.Name0 AS 'PC Name'
FROM
    v_Add_Remove_Programs arp 
    INNER JOIN v_R_System sys ON arp.ResourceID=sys.ResourceID
WHERE
    arp.DisplayName0 LIKE '%Java%' AND
    arp.Publisher0 LIKE '%Oracle%' AND
    arp.Version0 NOT LIKE '%6%'
GROUP BY
    arp.DisplayName0,
    arp.Version0,
    sys.Name0

Table Structure:

Product     Version                         PC Name 
Java        7 Update 10 7.0.100             PC1 
Java        7 Update 10 7.0.100             PC2 
Java        7 Update 11 7.0.110             PC3 
Java        7 Update 13 7.0.130             PC4 
Java        7 Update 13 7.0.130             PC5 
Java        7 Update 13 (64-bit) 7.0.130    PC6
Siyual
  • 16,415
  • 8
  • 44
  • 58
Rico
  • 7
  • 7
  • What does your data look like? The use of `LIKE` with both a leading and trailing wildcard looks concerning. – Siyual Jul 16 '15 at 14:15
  • The data shows PCs that have all versions of Java except for 6, but the problem is that some of these PCs ALSO have 6 installed along with 7 or 8. I want just the PCs with 7 or 8 installed but dont ALSO have 6 installed. – Rico Jul 16 '15 at 14:24
  • I mean, what does the data *physically* look like. Can you include sample data from your tables? – Siyual Jul 16 '15 at 14:26
  • Product Version PC Name Java 7 Update 10 7.0.100 PC1 Java 7 Update 10 7.0.100 PC2 Java 7 Update 11 7.0.110 PC3 Java 7 Update 13 7.0.130 PC4 Java 7 Update 13 7.0.130 PC5 Java 7 Update 13 (64-bit) 7.0.130 PC6 – Rico Jul 16 '15 at 14:30
  • For example PC4 has Java 7 installed, but ALSO has Java 6 installed. I don't want PC4 to show in this list if Java 6 is also installed. – Rico Jul 16 '15 at 14:37
  • Updated answer to filter those out. – Siyual Jul 16 '15 at 14:40

2 Answers2

0

Edit: After seeing the comments about some PC's having multiple versions installed, you'll want to use a NOT EXISTS query to filter out any PC that has version 6 installed. 

SELECT      DISTINCT 
            arp.DisplayName0        AS 'Product',
            arp.Version0            AS 'Version',
            sys.Name0               AS 'PC Name'
FROM        v_Add_Remove_Programs   arp 
INNER JOIN  v_R_System              sys ON  arp.ResourceID = sys.ResourceID
WHERE       arp.DisplayName0 = 'Java' 
AND NOT EXISTS
(
    SELECT      *
    FROM        v_Add_Remove_Programs   arp1
    INNER JOIN  v_R_System              sys1    ON  arp1.ResourceID = sys1.ResourceID
    WHERE       sys1.Name0 = sys.Name0
    AND         arp1.DisplayName0 = 'Java'
    AND         arp1.Version0 LIKE '6%'
)
Siyual
  • 16,415
  • 8
  • 44
  • 58
  • I think it is working now Siyual, I changed the display name to LIKE % and am getting hits now, thank you, you are awesome! – Rico Jul 16 '15 at 15:10
0

Another solution is to just exclude ResourceIds which have version 6:
SELECT arp.DisplayName0 AS 'Product', arp.Version0 AS 'Version', sys.Name0 AS 'PC Name' FROM v_Add_Remove_Programs arp INNER JOIN v_R_System sys ON arp.ResourceID=sys.ResourceID WHERE arp.DisplayName0 LIKE '%Java%' AND arp.Publisher0 LIKE '%Oracle%' AND arp.ResourceID NOT IN ( SELECT ResourceID FROM v_Add_Remove_Programs WHERE arp.DisplayName0 LIKE '%Java%' AND arp.Publisher0 LIKE '%Oracle%' AND arp.Version0 LIKE '%6%'
)
GROUP BY arp.DisplayName0, arp.Version0, sys.Name0

Matt
  • 151
  • 1
  • 7
  • Matt, I just tried yours and am still getting PCs that have both 6 and 7 installed on the same PC. – Rico Jul 16 '15 at 15:03
  • And Siyual, I get no records back from yours and I know there are PCs that have 7 and not 6 installed. – Rico Jul 16 '15 at 15:07