/build/static/layout/Breadcrumb_cap_w.png

Subselect Queries not giving any results

Hi, 

I am trying to find which systems are not in compliance with our newly deployed Virus Protection. I do this by querying for a specific file on all systems, then creating a subselect off of that original query. My issue is that I the subselect doesn't return any results. I know for a fact that a few machines should be popping up, but nothing is there. First query brings back accurate results. Can anybody point me in the right direction here?  

First Query looking for the file:

select distinct SMS_R_System.Name, SMS_R_System.Client, SMS_R_System.ClientType, SMS_R_System.ClientVersion, SMS_G_System_SoftwareFile.FileName, SMS_R_System.ResourceId 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 = "CylanceUi.exe"

Subselect query:

select distinct SMS_R_System.Name, SMS_R_System.Client, SMS_R_System.ClientType, SMS_R_System.ClientVersion, SMS_G_System_SoftwareFile.FileName, SMS_R_System.ResourceId from  SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_R_System.Name not in (select distinct SMS_R_System.Name, SMS_R_System.Client, SMS_R_System.ClientType, SMS_R_System.ClientVersion, SMS_G_System_SoftwareFile.FileName, SMS_R_System.ResourceId 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 = "CylanceUi.exe")


Any help or pointings in the right direction would be very helpful. All other Subselect queries I have which use Add Remove programs work properly. 




0 Comments   [ + ] Show comments

Answers (1)

Posted by: JasonEgg 7 years ago
Red Belt
0
This is just a guess. What if you change the second query's WHERE clause as follows:

WHERE SMS_R_System.NAME NOT IN (
SELECT DISTINCT SMS_R_System.NAME
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 = "CylanceUi.exe")

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ