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.