SMS Query

I need help with a SMS Query. I can search on FILE_NAME = "Acrobat.exe" and FILE_VERSION is less than "8.0%" OR I can search for FILE_NAME = ""Illustrator.exe" but not both at the same time. I am using a sub select and have tried many different things.

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 inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "Acrobat.exe" and SMS_G_System_SoftwareFile.FileVersion < "8.0%" and SMS_G_System_SYSTEM.Name not in (select  distinct 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_SoftwareFile on  SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where  SMS_G_System_SoftwareFile.FileName != "Illustrator.exe")
LVL 3
Mister_SpockAsked:
Who is Participating?
 
matrixnzConnect With a Mentor Commented:
Hi Mister_Spock

Not entirely sure what you're looking for but the following displays both Acrobat.exe with Version# less than 8.0 and also Illustrator.exe.

Cheers

select SMS_R_System.Name, SMS_G_System_SoftwareFile.FileName, SMS_G_System_SoftwareFile.FileVersion from  SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "Acrobat.exe" and SMS_G_System_SoftwareFile.FileVersion < "8.0%" or SMS_G_System_SoftwareFile.FileName = "illustrator.exe"

0
 
matrixnzCommented:
Hi Mister_Spock

Thanks for the points however can you tell me why you gave me a b grading, for future reference?

Cheers
0
 
Mister_SpockAuthor Commented:
I was looking for a way to use the not in keyword in a sub-select. I could always get both Acrobat.exe version < 8 and Illustrator.exe but I needed a way to only display Acrobat.exe if the PC did not have Illustrator.exe as well. After alot of research I went another way. Thank you for trying though.

where SMS_G_System_SoftwareFile.FileName = "Acrobat.exe" and SMS_G_System_SoftwareFile.FileVersion < "8.0%" and SMS_G_System_SYSTEM.Name not in (select  distinct 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_SoftwareFile on  SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where  SMS_G_System_SoftwareFile.FileName != "Illustrator.exe")
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.