select distinct s.name from salesperson s where s.sales_id not in (select distinct o.sales_id from orders o where o.com_id = 1)
I was trying to use this query which didn't take 'RED' into consideration. It didn't pass some of test cases. I was wondering why. Is it because there could be more than one company named 'RED'?
I think this is because you always assume the com_id for 'Red' is 1. However, in some case, it may change. Therefore you should always try to get the com_id from the company table.
Here is my code:Select S.name name from salesperson S left join (Select sales_id from orders where com_id in (select com_id from company where name = 'RED')) SS on S.sales_id = SS.sales_id where SS.sales_id is null;