Query with NOT EXISTS???

Question

Hello,

I have a query A:

Select DISTINCT SYS.Netbios_Name0 From v_R_System SYS
Join v_FullCollectionMembership COL on COL.ResourceID = SYS.ResourceID
WHERE

COL.CollectionID = 'UCP00020'

the result is giving 1,591 items...

then a query B:

Select DISTINCT SYS.Netbios_Name0, SYS.User_Name0, SP.ProductName, SP.CompanyName, SP.ProductVersion
From v_GS_SoftwareProduct SP
Join v_R_System SYS on SP.ResourceID = SYS.ResourceID
Join v_FullCollectionMembership COL on COL.ResourceID = SYS.ResourceID
Where SP.ProductName Like 'Cisco AMP%'

AND COL.CollectionID = 'UCP00020'

the result is giving 1,021 items

when I am doing the combined query A & B in a query C

Select DISTINCT SYS.Netbios_Name0 From v_R_System SYS
Join v_FullCollectionMembership COL on COL.ResourceID = SYS.ResourceID
WHERE
COL.CollectionID = 'UCP00020'
AND NOT EXISTS (
Select DISTINCT SYS.Netbios_Name0, SYS.User_Name0, SP.ProductName, SP.CompanyName, SP.ProductVersion
From v_GS_SoftwareProduct SP
Join v_R_System SYS on SP.ResourceID = SYS.ResourceID
Join v_FullCollectionMembership COL on COL.ResourceID = SYS.ResourceID
Where SP.ProductName Like 'Cisco AMP%'

AND COL.CollectionID = 'UCP00020')

the result is 0 item ... what is wrong... 1,591-1,021= 570!!! ??

I tried as well

Select DISTINCT SYS.Netbios_Name0 From v_R_System SYS
Join v_FullCollectionMembership COL on COL.ResourceID = SYS.ResourceID
WHERE
COL.CollectionID = 'UCP00020'
AND NOT EXISTS (
Select Distinct SYS.Netbios_Name0 from v_R_System SYS
join v_GS_SoftwareProduct SP on SP.ResourceID = SYS.ResourceID
Join v_FullCollectionMembership COL on COL.ResourceID = SYS.ResourceID
Where SP.ProductName Like 'Cisco AMP%'

AND COL.CollectionID = 'UCP00020')

still 0 item when I use NOT EXISTS selection....!!! Why ?

I tried NVL function but it says "NVL is not recognized as a built-in function name"!!!

Thanks,
Dom

Answer 1

Select DISTINCT SYS.Netbios_Name0 From v_R_System SYS
Join v_FullCollectionMembership COL on COL.ResourceID =
SYS.ResourceID
WHERE
COL.CollectionID = 'UCP00020'
AND NOT EXISTS (
Select DISTINCT SYS.Netbios_Name0, SYS.User_Name0,
SP.ProductName, SP.CompanyName, SP.ProductVersion
From v_GS_SoftwareProduct SP
Join v_R_System SYS on SP.ResourceID = SYS.ResourceID
Join v_FullCollectionMembership COL on COL.ResourceID =
SYS.ResourceID
Where SP.ProductName Like 'Cisco AMP%'
AND COL.CollectionID = 'UCP00020')

When you use EXISTS / NOT EXISTS, the subquery should always be correlated to the outer query. What happens (logically) is that the subquery is evaluated for every row in the outer query.

But if there is no correlation between the queries, this means that the query will return either all rows in the outer table, or no rows at all. And since you have NOT EXISTS and the subquery returns rows, you get no rows back from the outer query.

Now, I don't know your tables, but I guess that this is the query you want_

Select DISTINCT SYS.Netbios_Name0 From v_R_System SYS
Join v_FullCollectionMembership COL on COL.ResourceID = SYS.ResourceID
WHERE
COL.CollectionID = 'UCP00020'
AND NOT EXISTS (
Select DISTINCT SYS.Netbios_Name0, SYS.User_Name0, SP.ProductName, SP.CompanyName, SP.ProductVersion
From v_GS_SoftwareProduct SP
Join v_R_System SYS on SP.ResourceID = SYS.ResourceID
Where SP.ProductName Like 'Cisco AMP%'
AND COL.CollectionID = 'UCP00020'
AND COL.ResourceID = SYS.ResourceID)

If you compare, you will see that I removed the join to v_FullCollectionMembership from the subquery, and moved the join condition to the WHERE clause.

Answer 2

Hello

This is the final query ...

Select DISTINCT SYS0.Netbios_Name0 From v_R_System SYS0
Join v_FullCollectionMembership COL on COL.ResourceID = SYS0.ResourceID
WHERE
COL.CollectionID = 'UCP00020'
AND (SYS0.Netbios_Name0 NOT IN(
Select DISTINCT SYS.Netbios_Name0
From v_GS_SoftwareProduct SP
Join v_R_System SYS on SP.ResourceID = SYS.ResourceID
Join v_FullCollectionMembership COL on COL.ResourceID = SYS.ResourceID
Where SP.ProductName Like 'Cisco AMP%'
AND COL.CollectionID = 'UCP00020')
AND SYS0.Netbios_Name0 NOT IN(
Select SYS.Netbios_Name0
From v_GS_SoftwareProduct SP
Join v_R_System SYS on SP.ResourceID = SYS.ResourceID
Join v_FullCollectionMembership COL on COL.ResourceID = SYS.ResourceID
Where SP.ProductName Like 'FireEye Agent%'
AND COL.CollectionID = 'UCP00020'))

it seems to give the correct result... any comments?

Thanks,
Dom

Answer 3

Hi @Dominique DUCHEMIN ,

Welcome to Microsoft Q&A!

Please refer below which is one example of using NOT EXISTS:

SELECT ProductID, ProductName   
FROM Northwind..Products p  
WHERE NOT EXISTS (  
    SELECT 1   
    FROM Northwind..[Order Details] od   
    WHERE p.ProductId = od.ProductId)  

In the last row inside NOT EXISTS part, we have to add one join (p.ProductId = od.ProductId) to create one connetion between the query and subquery otherwise we will get wrong result.

So in your query, you have to add one connetion between the query and subquery as below:

Select DISTINCT SYS.Netbios_Name0 From v_R_System SYS  
Join v_FullCollectionMembership COL on COL.ResourceID = SYS.ResourceID  
WHERE  
COL.CollectionID = 'UCP00020'  
AND NOT EXISTS (  
Select DISTINCT SYS1.Netbios_Name0, SYS1.User_Name0, SP.ProductName, SP.CompanyName, SP.ProductVersion  
From v_GS_SoftwareProduct SP  
Join v_R_System SYS1 on SP.ResourceID = SYS1.ResourceID  
Join v_FullCollectionMembership COL1 on COL1.ResourceID = SYS1.ResourceID  
Where SP.ProductName Like 'Cisco AMP%'  
AND COL1.CollectionID = 'UCP00020'  
and  SYS1.ResourceID=SYS.ResourceID)  

Or you could simplify your query as below:

Select DISTINCT SYS.Netbios_Name0   
From v_R_System SYS  
Join v_FullCollectionMembership COL on COL.ResourceID = SYS.ResourceID  
WHERE COL.CollectionID = 'UCP00020'  
AND NOT EXISTS (  
Select 1  
From v_GS_SoftwareProduct SP  
where SP.ResourceID = SYS.ResourceID  
and SP.ProductName Like 'Cisco AMP%'  
)  

In addition, we could also use NOT IN as below:

Select DISTINCT SYS.Netbios_Name0   
From v_R_System SYS  
Join v_FullCollectionMembership COL on COL.ResourceID = SYS.ResourceID  
WHERE COL.CollectionID = 'UCP00020'  
AND  SYS.ResourceID NOT IN (  
Select SP.ResourceID  
From v_GS_SoftwareProduct SP  
where SP.ProductName Like 'Cisco AMP%'  
)  

If all of above are not working, we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Hello,

I have the first step working...

Select DISTINCT SYS0.Netbios_Name0 From v_R_System SYS0
Join v_FullCollectionMembership COL on COL.ResourceID = SYS0.ResourceID
WHERE
COL.CollectionID = 'UCP00020'
AND SYS0.Netbios_Name0 NOT IN(
Select DISTINCT SYS.Netbios_Name0 from v_R_System SYS
join v_GS_SoftwareProduct SP on SP.ResourceID = SYS.ResourceID
Join v_FullCollectionMembership COL on COL.ResourceID = SYS.ResourceID
Where SP.ProductName Like 'Cisco AMP%'

AND COL.CollectionID = 'UCP00020')

working to add a select subquery...

Select SYS.Netbios_Name0, SYS.User_Name0, SP.ProductName, SP.CompanyName, SP.ProductVersion
From v_GS_SoftwareProduct SP
Join v_R_System SYS on SP.ResourceID = SYS.ResourceID
Join v_FullCollectionMembership COL on COL.ResourceID = SYS.ResourceID
Where SP.ProductName Like 'FireEye Agent%'

AND COL.CollectionID = 'UCP00020')

Thanks,
Dom