question

DominiqueDUCHEMIN-4668 avatar image
0 Votes"
DominiqueDUCHEMIN-4668 asked MelissaMa-msft commented

Query with NOT EXISTS???

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

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DominiqueDUCHEMIN-4668 avatar image
0 Votes"
DominiqueDUCHEMIN-4668 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DominiqueDUCHEMIN-4668 avatar image
0 Votes"
DominiqueDUCHEMIN-4668 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered DucheminDominique-7551 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks let me try this...

0 Votes 0 ·
MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered MelissaMa-msft commented

Hi @DominiqueDUCHEMIN-4668,

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks let do a test on this and see which one is the closest of what I need...

0 Votes 0 ·
MelissaMa-msft avatar image MelissaMa-msft DucheminDominique-7551 ·

Hi @DominiqueDUCHEMIN-4668,

Could you please provide any update? Thanks.

Best regards
Melissa

0 Votes 0 ·