SQL Exercises: Find all the salesmen worked for only one customer

SQL SUBQUERY: Exercise-17 with Solution

17. From the following tables write a SQL query to find salespeople who deal with a single customer. Return salesman_id, name, city and commission.

customer_id  cust_name     city        grade       salesman_id
-----------  ------------  ----------  ----------  -----------
3002         Nick Rimando  New York    100         5001
3005         Graham Zusi   California  200         5002
3001         Brad Guzan    London      100         5005
3004         Fabian Johns  Paris       300         5006
3007         Brad Davis    New York    200         5001
3009         Geoff Camero  Berlin      100         5003
3008         Julian Green  London      300         5002
3003         Jozy Altidor  Moncow      200         5007

salesman_id  name        city        commission
-----------  ----------  ----------  ----------
5001         James Hoog  New York    0.15
5002         Nail Knite  Paris       0.13
5005         Pit Alex    London      0.11
5006         Mc Lyon     Paris       0.14
5003         Lauson Hen  San Jose    0.12
5007         Paul Adam   Rome        0.13

Sample Solution:

-- Selecting all columns from the 'salesman' table
SELECT * 
-- Specifying the table to retrieve data from ('salesman')
FROM salesman 
-- Filtering the results based on the condition that 'salesman_id' is in the result of a subquery
WHERE salesman_id IN (
   -- Subquery: Selecting distinct 'salesman_id' from the 'customer' table (aliased as 'a')
   SELECT DISTINCT salesman_id 
   -- Specifying the table to retrieve data from ('customer' as 'a')
   FROM customer a 
   -- Checking the non-existence of records in a subquery
   WHERE NOT EXISTS (
      -- Subquery: Selecting any record from the 'customer' table (aliased as 'b') where 'salesman_id' matches the outer query's 'salesman_id' and 'cust_name' is not equal
      SELECT * FROM customer b 
      -- Specifying the table to retrieve data from ('customer' as 'b')
      WHERE a.salesman_id = b.salesman_id 
      AND a.cust_name <> b.cust_name
   )
);

Output of the Query:

salesman_id	name		city	commission
5005		Pit Alex	London	0.11
5006		Mc Lyon		Paris	0.14
5007		Paul Adam	Rome	0.13
5003		Lauson Hen	San Jose0.12

Explanation:

The said SQL query that selects all columns from the 'salesman' table where the "salesman_id" column appears in a subquery. The subquery selects the distinct "salesman_id" values from the 'customer' table (aliased as 'a') where there does not exist at least one record in the same 'customer' table (aliased as 'b') where the "salesman_id" value in 'a' is equal to the "salesman_id" value in 'b' and the "cust_name" value in 'a' is not equal to the "cust_name" value in 'b'.
In other words, it's retrieving all the rows from the 'salesman' table where the salesman has all customers with the same name. It is using the IN clause which is used to filter the rows from the table based on the values returned by the subquery. The NOT EXISTS clause is used to filter out the rows that do not match the condition.

Visual Explanation:

Practice Online

Sample Database: inventory

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: Find the salesmen who have multiple customers.
Next SQL Exercise: Show all salesmen with more than one order.