CODEWITHSUNDEEP
sqlsubquerywhere-clause
rajat shakya
rajat shakya

Reputation: 25

Can someone please explain the query shown in depth?

Question: write a SQL query to find the salespeople from the given tables who had more than one customer. Return salesman_id and name.

Sample table: customer

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

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

Solution

SELECT salesman_id,name 
FROM salesman a 
WHERE 1 < (SELECT COUNT(*) 
           FROM customer 
           WHERE salesman_id = a.salesman_id);

So basically am unable to understand the subquery how can we compare two table column without using the joins and secondly how this where condition is working as a group by as well and provide the count. So if someone can help me understand this subquery in depth with easy language then it would be really helpful.

This question and solution is correct because am practicing on W3resource and you can find this question under the topic of SQL Subquery Exercises in the exercise 11.

Upvotes: 0

Views: 176

Answers (1)

shree.pat18
shree.pat18

Reputation: 21757

This is an example of a correlated subquery. In effect, the subquery is run once for each matching row of the outer table, using the join condition as filter criteria i.e. it will count for all rows with id = first id from outer table, then id = second id from outer table and so on. The resulting count for each id is then passed to the WHERE clause for filtering the rows of the outer table.

You could also write an equivalent query like so:

SELECT s.salesman_id,s.name 
FROM salesman a
INNER JOIN customer b
ON a.salesman_id = b.salesman_id
GROUP BY a.salesman_id
HAVING count(b.customer_id) >  1

Upvotes: 0

Related Questions