From the following tables, write a SQL query to find those salespersons who have same cities where customer lives as well as do not have customers in their cities and indicate it by ‘NO MATCH’

From the following tables, write a SQL query to find those salespersons who have same cities where customer lives as well as do not have customers in their cities and indicate it by ‘NO MATCH’. Sort the result set on 2nd column (i.e. name) in descending order. Return salesperson ID, name, customer name, commission.

Sample table: Salesman

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
        5007 | Paul Adam  | Rome     |       0.13
        5003 | Lauson Hen | San Jose |       0.12

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

SELECT salesman.salesman_id, name, cust_name, commission FROM salesman, customer WHERE salesman.city = customer.city UNION (SELECT salesman_id, name, 'NO MATCH', commission FROM salesman WHERE NOT city = ANY (SELECT city FROM customer)) ORDER BY 2 DESC

salesman_id name cust_name commission 5005 Pit Alex Julian Green 0.11 5005 Pit Alex Brad Guzan 0.11 5007 Paul Adam NO MATCH 0.13 5002 Nail Knite Fabian Johnson 0.13 5006 Mc Lyon Fabian Johnson 0.14 5003 Lauson Hen NO MATCH 0.12 5001 James Hoog Nick Rimando 0.15 5001 James Hoog Brad Davis 0.15

From the following tables, write a SQL query to find those salespersons who have same cities where customer lives as well as do not have customers in their cities and indicate it by ‘NO MATCH’

All Answers

Similar questions

need a help?

find thousands of online teachers now