not exists but not returning any results

mfm5105 0
Viewed 29

There is 1 table.

ParentServiceCategoryID ServiceName Entity TypeID mapped
1 landscape 5
1 landscape 6
1 landscape 7
1 Trimmings 88
1 Trimmings 8
1 Trimmings 99

There are 3 services this company does: Landscape, Trimmings, and Shoveling.
Parent Service ID is called 'Outdoor Services'

I am trying to identify Outdoor Services that do not have 'Shoveling'. Example above would be captured. If the service had "shoveling' the same parent service category (Outdoor Services) then I do not want this captured.

Trying to do 'Not Exists' but returning no results

attempted code:

SELECT * 
FROM table1 t1
WHERE NOT EXISTS
     (select * FROM table1 t2 
       where t1.ParentServiceCategoryID=t2.ParentServiceCategoryID
       AND t2.ServiceName='Shoveling'
     )
not-exists subquery sql-server
1 Answers

using you're Query it's returning data except 'Shoveling'

declare @Table1 as table  
(
ParentServiceCategoryID int,    ServiceName varchar(8000),
EntityTypeIDmapped int)  
insert into @Table1
values  
(1, 'landscape',    5 ),  
(1, 'landscape',    6 ),  
(1, 'landscape',    7 ),  
(1, 'Trimmings',    88),  
(1, 'Trimmings',    8 ),  
(1, 'Trimmings',    99)  

SELECT * 
FROM @table1 t1
WHERE NOT EXISTS  
     (select * FROM @table1 t2   
       where t1.ParentServiceCategoryID=t2.ParentServiceCategoryID   
       AND t2.ServiceName='Shoveling'
     )

You can achieve this above data by using below Query

SELECT * 
FROM @Table1 t1
where ServiceName not in('Shoveling')
Atul Pathrikar0
Related