select s.name from salesperson s where s.sales_id not in ( select distinct o.sales_id from orders o, company c where c.com_id = o.com_id and c.name ='RED')
I firstly want to join salesperson to the subquery where c.name != 'RED'. but it turns out not working. Any one know why?
All comments appreciate.
@luckymyli same issue, don't know why
Actually, it is easy to understand
when using code like this
select * from salesperson s join orders o on s.sales_id = o.sales_id join company c on o.com_id = c.com_id;
- we miss some salesperson doesn't finish any order, so one way to improve is use LEFTJOIN things
- we can't not just filter out the one who finishes order with RED, see the below image for more detail
in this case, if we use != RED, the Pam with YELLOW will be count in, while that's something we don't want.
So we have to choose the ones have order with RED, and then use subquery to filter them out.