SQL Statement NOT EXISTS

Question

Hello,

select v_R_SYSTEM.ResourceID from v_R_System

WHERE NOT EXISTS(
select v_R_SYSTEM.ResourceID
/* ,v_R_SYSTEM.ResourceType,v_R_SYSTEM.Name0,v_R_SYSTEM.SMS_Unique_Identifier0,
v_R_SYSTEM.Resource_Domain_OR_Workgr0,v_R_SYSTEM.Client0, v_GS_ADD_REMOVE_PROGRAMS.DisplayName0, v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0
*/
from v_R_System
left join v_GS_ADD_REMOVE_PROGRAMS
on v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_R_System.ResourceId
left join v_GS_ADD_REMOVE_PROGRAMS_64
on v_GS_ADD_REMOVE_PROGRAMS_64.ResourceID = v_R_System.ResourceId
where
v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 = 'FireEye Endpoint Agent'
or v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0 = 'FireEye Endpoint Agent')

Blockquote

This is giving me a result of 0

If I run

select v_R_SYSTEM.ResourceID from v_R_System

I have 52,648 records...

if I run

Blockquote

select v_R_SYSTEM.ResourceID
/* ,v_R_SYSTEM.ResourceType,v_R_SYSTEM.Name0,v_R_SYSTEM.SMS_Unique_Identifier0,
v_R_SYSTEM.Resource_Domain_OR_Workgr0,v_R_SYSTEM.Client0, v_GS_ADD_REMOVE_PROGRAMS.DisplayName0, v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0
*/
from v_R_System
left join v_GS_ADD_REMOVE_PROGRAMS
on v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_R_System.ResourceId
left join v_GS_ADD_REMOVE_PROGRAMS_64
on v_GS_ADD_REMOVE_PROGRAMS_64.ResourceID = v_R_System.ResourceId
where
v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 = 'FireEye Endpoint Agent'
or v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0 = 'FireEye Endpoint Agent''

I have 20,983

How to get the difference between the 52,648 and the 20,983?

Thanks,
Dom

Answer 1

Check if this non-minimised combination of your two queries gives the expected results:

select v_R_SYSTEM.ResourceID from v_R_System
except
select v_R_SYSTEM.ResourceID from v_R_System
left join v_GS_ADD_REMOVE_PROGRAMS on etc....

Answer 2

can you please exact what is your query

Answer 3

Hello,

select sys1.ResourceID from v_R_System sys1
WHERE NOT EXISTS(
select *
from v_R_System sys2
left join v_GS_ADD_REMOVE_PROGRAMS
on v_GS_ADD_REMOVE_PROGRAMS.ResourceID = sys2.ResourceId
left join v_GS_ADD_REMOVE_PROGRAMS_64
on v_GS_ADD_REMOVE_PROGRAMS_64.ResourceID = sys2.ResourceId
where
sys1.ResourceID = sys2.ResourceID and (
v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 = 'FireEye Endpoint Agent'
or v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0 = 'FireEye Endpoint Agent'))

Seems to work...
but I need it in WQL now to integrate in Configuration Manager collection ...

Thanks,
Dom

Answer 4

To start with: to insert code samples, use the button with ones and zeroes on it.

Next, the reason you NOT EXISTS query return zero rows is because there is no correlation back from the subquery to the main query. When you use NOT EXISTS this way, you will either get all rows back (if the subquery returns no rows), or no rows back (if the subquery returns at least one row.) Because that is meaning of [NOT] EXISTS: evaluate (logically) the subquery for every row in the outer table, and if at least one row is return, return true for EXISTS and false for NOT EXISTS.

I think this query may return what you are looking for:

select v_R_SYSTEM.ResourceID 
from v_R_System
WHERE NOT EXISTS(
        select *
        from   v_GS_ADD_REMOVE_PROGRAMS
        where  v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_R_System.ResourceId
          and v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 = 'FireEye Endpoint Agent')
      AND NOT EXISTS (
         SELECT *
         FROM   v_GS_ADD_REMOVE_PROGRAMS_64
         WHERE  v_GS_ADD_REMOVE_PROGRAMS_64.ResourceID = v_R_System.ResourceId
           AND  v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0 = 'FireEye Endpoint Agent')

Answer 5

Aslo you can UNION for the same.
Use 1 Instead of * for query optimize

select v_R_SYSTEM.ResourceID 
from v_R_System
WHERE NOT EXISTS(
        select 1
        from   v_GS_ADD_REMOVE_PROGRAMS
        where  v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_R_System.ResourceId
          and v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 = 'FireEye Endpoint Agent'

UNION 

         SELECT 1
         FROM   v_GS_ADD_REMOVE_PROGRAMS_64
         WHERE  v_GS_ADD_REMOVE_PROGRAMS_64.ResourceID = v_R_System.ResourceId
           AND  v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0 = 'FireEye Endpoint Agent')