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
Yep, no need for
select name from salespersonbelow considers everybody from the
salesperson table regardless of whether they appear in the
orders table or not.
select name from salesperson where sales_id not in ( select o.sales_id from orders o join company c on o.com_id = c.com_id where c.name = 'red' )
Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.