The solution is i need to remove the not and add where c.ItemID is null
Ok i'm revisiting this concept again. It is not working. Again, i kinda see why this is not working. But i don't fully understand why it isn't cut and dry.
Why does the following code return all of the values in the left table with null values of the right table. I would expect it to join where it could and then evaluate the rest of the conditions. I would not expect it to create a result set such that there is no join--when the item are indeed in the right table. I could see if they were not int the right table. (Its kinda unexpected that it would create this result set.)
create table dbo.Item (
create table dbo.Collection (
insert into Item (ItemID, Name)
values (1, 'Triangle'),
insert into Collection (ItemID, name, Status)
values (1, 'Triangle', 'HLD'),
(2, 'Cirle', 'HLD'),
(3, 'Square', 'HLD'),
(4, 'Rectangle', 'HLD'),
(5, 'Octagon', 'HLD')
from Item i (nolock)
left join Collection c (nolock) on i.ItemID = c.ItemID and c.Status not in ('HLD', 'INC', 'PND', 'CNL')
What i want it to do is an except.
select ItemID from item
select itemID from collection
This is not what the above code is doing. Is the only solution to use a subquery NOT IN or NOT EXIST as alternatives?