Get suppliers that doesn’t belong to a category in another table

I’m looking for a query where I need to show all the Suppliers from the Suppliers table that doesn’t have products from category 1 (Products.CategoryID = 1). Whenever I run it it always gives an error.

Select SupplierID From Suppliers su
where SupplierID NOT IN
(select distinct SupplierID from Products 
where SupplierID in 
(select SupplierID from Products where CategoryID=1)

Side question: How do I get these results but with suppliers that has products from cat. 6 ? (So none from cat1 but does have from cat6).

Answer

Rather than using sub-selects, try to use set based operations like joins or exists. One option for your situation is below, though there are several ways to achieve what you are looking to do. Which one is best will depend on your data:

select su.SupplierID
From Suppliers as su
where not exists(select null -- The only check here is for a record, so you can select any value and it won't change the functionality
                 from Products as p
                 where su.SupplierID = p.SupplierID
                   and p.CategoryID = 1
                )