There are three tables
Salesperson

ID Name Age Salary 1 Tom 61 140000 2 Michael 34 44000 5 Chris 34 40000 7 Dan 41 52000 8 Ken 57 115000 11 Joe 38 38000

Customer

 ID Name City Industry Type 4 IVM New York J 6 Panosong Florida J 7 Seamens Chicago B 9 Nowkia Houston B

Orders

 Number order_date cust_id salesperson_id Amount 10 8/2/96 4 2 540 20 1/30/99 4 8 1800 30 7/14/95 9 1 460 40 1/29/98 7 2 2400 50 2/3/98 6 7 600 60 3/2/98 6 7 720 70 5/6/98 9 7 150

It is necessary to display the names of all vendors who do not have orders from Seamens. How should we join tables using an inner join?

  • and tried? WHERE customer.Name <> 'Seamens'; - JVic
  • Not that, maybe I’m not making the right request - Andrei

2 answers 2

 SELECT Salesperson.Name FROM Orders JOIN Customer ON Orders.cust_id = Customer.ID JOIN Salesperson ON Orders.salesperson_id = Salesperson.ID WHERE Customer.Name != 'Seamens' GROUP BY Orders.salesperson_id
  • Immediately select all sellers who have orders from all but Siemens. And we need all those who have no orders from Siemens. These are slightly different conditions - Okdel
  • Yes, I understand, I'll fix it now. Recently there was a similar task - Maxim Stepanov
  • No need to correct. The author found the answer correct. Apparently the condition described by the author is not correct. - Okdel
  • @Okdel Yes, it’s strange, I’m wrong now, the sellers get into the list, they have sold something, including Siemens, if there were sales to others. - Maxim Stepanov

Use EXISTS

 SELECT s.name FROM Salesperson s WHERE NOT EXISTS ( SELECT 1 FROM Orders o, Customer c WHERE o.salesperson_id = s.id AND c.id = o.cust_id AND c.name = 'Seamens' )