Sales Person


  • 0

    Click here to see the full article post


  • 0
    L

    WHY WE NEED LEFT JOIN HERE?


  • 0
    H
    This post is deleted!

  • 0
    H

    @lightning_mi For this particular problem, you can use inner join if this is what you are asking. The solution is trying to focus on looking through ALL of the orders.


  • 0
    P

    @lightning_mi
    from what I understood about the problem was that, it is asking for all the salespeople who didn't have a business deal with some company, a company can have many sales people who did not make a deal at all, so they do not figure in the orders table. hence the left outer join on the salesperson tables is required.


  • 0
    V

    I wrote my code below, not sure why it is wrong. Anyone know the reason? Thanks a lot for the help.

    select distinct s.name
    from salesperson s
    left join orders o
    on s.sales_id=o.sales_id
    where o.sales_id not in (
    select sales_id from orders where com_id=1
    )


  • 0
    Z

    @prithwin I still think left outer join us unnecessary here. Since we use "not in" here, so we eliminate salespersons that we've already known who had dealt with company RED. To gain this info we only need inner join. Salesperson who made no deals with any company will be picked out in the outer query.


  • 0
    R
    SELECT 
        name
    FROM
        salesperson
    WHERE
        name
            NOT IN (
                    SELECT 
                         salesperson.name
                    FROM 
                        orders ord
                        LEFT JOIN company  comp
                            ON ord.com_id = comp.com_id
                        JOIN salesperson 
                            ON ord.sales_id = salesperson.sales_id
                    WHERE 
                        comp.name = 'RED'
                    )
    

  • 0

    Credit to @tongzhou2

    select salesperson.name
    from orders o join company c on (o.com_id = c.com_id and c.name = 'RED')
    right join salesperson on salesperson.sales_id = o.sales_id
    where o.sales_id is null
    

Log in to reply
 

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