CODEWITHSUNDEEP
mysql
mnickey
mnickey

Reputation: 787

Query with exclusion

I'm trying to write a query finding all the salespersons names that do not have orders with a company.

The query I have is:

SELECT Name
FROM Salesperson
JOIN Orders ON orders.salesperson_id = Salesperson.ID
    WHERE cust_id <> 4
GROUP BY Name;

While this somewhat works, I feel that I should be more explicit. If you could help guide me to where I am going wrong, I'd appreciate the help. What I am expecting to see is...

Abe
Chris
Dan
Joe

The tables are:

SalesPerson

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

Customer

ID    Name    City    Industry
4   Samsonic    plesant J
6   Panasung    oaktown J
7   Samony  Jackson B
9   Orange  Jackson B

Orders

Number    Date    cust_id    salesperson_id    amount
10  1996-08-02  4   2   540
20  1999-01-30  4   8   1800
30  1995-07-14  9   1   460
40  1998-01-29  7   2   2400
50  1998-02-03  6   7   600
60  1998-03-02  6   7   720
70  1998-05-06  9   7   150

Upvotes: 0

Views: 35

Answers (1)

Daniele Cappuccio
Daniele Cappuccio

Reputation: 2202

When joining together those two tables, you get only the ids which show up in both tables and whose cust_id value is not 4, since your join is not an OUTER JOIN, i.e. ids = 1, 2, 7.

Then, when you state SELECT Name, you obtain this:

Abe   // id = 1
Bob   // id = 2
Dan   // id = 7

(The GROUP BY statement is completely useless in this case, since you do not use any COUNT(...), SUM(...) or similar in the SELECT)
If you want to avoid repetition, you can replace the GROUP BY statement using SELECT DISTINCT instead of SELECT.

From the result you're expecting, I suppose you want to exclude all the ids of SalesPerson who made an order with cust_id = 4.

This should work smoothly:

SELECT DISTINCT Name
FROM SalesPerson
WHERE ID NOT IN (SELECT salesperson_id FROM Orders WHERE cust_id = 4)

Upvotes: 1

Related Questions