Help with a select statement

---

Greetings,
I am an SMS administrator and use SQL to create reports. My SQL skills are junior at best. I am trying to create an SQL select statement that shows me all computers that do not Java 1.5.0_04 installed. It is easy for me to search for all machines that have 'J2SE Runtime Environment 5.0 Update 4' but what I want is all computers minus the computers with Java 1.5.0_04.
Posted below is my attempt, but it does not work. Can someone lend a hand and direct me onto the correct path?
Thanks
_________________
select SMS_G_System_SYSTEM.Name, SMS_R_System.LastLogonUserName,
SMS_R_System.OperatingSystemNameandVersion, SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName,
SMS_R_System.ADSiteName
from SMS_R_System
inner join SMS_G_System_SYSTEM
on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId
inner join SMS_G_System_ADD_REMOVE_PROGRAMS
on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId
where SMS_R_System.OperatingSystemNameandVersion like "%Workstation 5.1%"
and SMS_G_System_SYSTEM.Name
not in (select SMS_G_System_SYSTEM.Name
from SMS_R_System
inner join SMS_G_System_SYSTEM
on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId
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 = "J2SE Runtime Environment 5.0 Update 4")

Sorry, I had to reformat your SQL so that it was comfortable for me to deal with:
SELECT
G.Name,
R.LastLogonUserName,
R.OperatingSystemNameandVersion,
G_ARP.DisplayName,
R.ADSiteName
FROM
SMS_R_System R
INNER JOIN
SMS_G_System_SYSTEM G ON R.ResourceId = G.ResourceID
INNER JOIN
SMS_G_System_ADD_REMOVE_PROGRAMS G_ARP ON R.ResourceId = G_ARP.ResourceID
WHERE
R.OperatingSystemNameandVersion LIKE '%Workstation 5.1%' AND
G.Name NOT IN
(
SELECT
G.Name
FROM
SMS_R_System R
INNER JOIN
SMS_G_System_SYSTEM G ON R.ResourceId =G.ResourceID
INNER JOIN
SMS_G_System_ADD_REMOVE_PROGRAMS G_ARP ONR.ResourceId = G_ARP.ResourceID
WHERE
G_ARP.DisplayName = 'J2SE Runtime Environment 5.0Update 4'
)
What you have there will return you all of the comuters where theOperatingSystemNameandVersion contains 'Workstation 5.1', but 'J2SERuntime Environment 5.0 Update 4' is not found in the DisplayName ofthe related Add_Remove_Programs table.
You should be using ResourceID, not Name, in your NOT IN clause,shouldn't you? Isn't that the field used to join the tablestogether? Like this:
SELECT
G.Name,
R.LastLogonUserName,
R.OperatingSystemNameandVersion,
G_ARP.DisplayName,
R.ADSiteName
FROM
SMS_R_System R
INNER JOIN
SMS_G_System_SYSTEM G ON R.ResourceId = G.ResourceID
INNER JOIN
SMS_G_System_ADD_REMOVE_PROGRAMS G_ARP ON R.ResourceId = G_ARP.ResourceID
WHERE
R.OperatingSystemNameandVersion LIKE '%Workstation 5.1%' AND
G.ResourceID NOT IN
(
SELECT
G.ResourceID
FROM
SMS_R_System R
INNER JOIN
SMS_G_System_SYSTEM G ON R.ResourceId =G.ResourceID
INNER JOIN
SMS_G_System_ADD_REMOVE_PROGRAMS G_ARP ONR.ResourceId = G_ARP.ResourceID
WHERE
G_ARP.DisplayName = 'J2SE Runtime Environment 5.0Update 4'
)
|||Acutally now that I look over the resulting data I think it was working. I was looking at one idividual who does have the software that was showing up on the list, but he just installed it this week and I only inventory weekly... duh!
Thanks for your help