Anyone have better idea not using subquery?


  • 0
    L
    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.


  • 0
    M

    @luckymyli same issue, don't know why


  • 0

    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;
    
    1. we miss some salesperson doesn't finish any order, so one way to improve is use LEFTJOIN things
    2. we can't not just filter out the one who finishes order with RED, see the below image for more detail

    0_1508369512635_temp.png

    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.


Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.