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

Hi @Dominique DUCHEMIN ,

Or try:

    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'  
    AND v_GS_ADD_REMOVE_PROGRAMS.ResourceID =v_R_SYSTEM.ResourceID  
    AND v_GS_ADD_REMOVE_PROGRAMS_64.ResourceID =v_R_SYSTEM.ResourceID)  

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.