Click here to see the full article post
@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.
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.
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
where o.sales_id not in (
select sales_id from orders where com_id=1
@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.
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' )
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
Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.