SQL Statement NOT EXISTS

Dominique DUCHEMIN 831 Reputation points
2021-05-14T19:04:19.143+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,266 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,516 questions
{count} votes

6 answers

Sort by: Most helpful
  1. Viorel 109.8K Reputation points
    2021-05-14T19:23:30.877+00:00

    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....

    1 person found this answer helpful.
    0 comments No comments

  2. Pratik Thummar 21 Reputation points
    2021-05-14T19:15:30.937+00:00

    can you please exact what is your query

    0 comments No comments

  3. Dominique DUCHEMIN 831 Reputation points
    2021-05-14T19:51:46.717+00:00

    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

    0 comments No comments

  4. Erland Sommarskog 98,911 Reputation points
    2021-05-14T21:27:57.837+00:00

    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')
    
    0 comments No comments

  5. Pratik Thummar 21 Reputation points
    2021-05-15T06:29:16.637+00:00

    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')