Sales Person
Problem
SalesPerson, Company, Orders. Return names of salespeople who did NOT have any order with company 'RED'.
Input
sp=[(1,'John'),(2,'Amy')] co=[(1,'RED'),(2,'ORANGE')] ord=[(1,'2014-01-01',1,1,100)]Output
['Amy']John sold to RED (com_id 1); Amy did not.
SELECT name
FROM SalesPerson
WHERE sales_id NOT IN (
SELECT o.sales_id FROM Orders o JOIN Company c ON o.com_id = c.com_id WHERE c.name = 'RED'
);Explanation
We want salespeople who never sold to the company named 'RED'. The clean way is to first build the list of people who did sell to RED, then keep everyone not in that list.
The inner subquery joins Orders to Company and filters to c.name = 'RED', producing every sales_id that ever had a RED order. This is our "banned" set.
The outer query then selects names from SalesPerson whose sales_id is NOT IN that banned set. Anyone with zero RED orders survives.
Example: John (id 1) has an order to com_id 1, which is RED, so John lands in the banned set and is dropped. Amy (id 2) has no RED order, so NOT IN keeps her — the result is ['Amy'].