CODEWITHSUNDEEP
sqlsql-serverinner-join
fnklstn
fnklstn

Reputation: 492

Using inner join instead of subquery

I wrote a query using a subquery, but I wonder if there is any way to write it using only inner join (or other joins) since it's more efficient. 

/*2.    List the name of the sales rep who serves the most customers*/

select Sr.REP_NUM, Fname, Lname, count(cust_num) As TotalCustomers from employee Em
inner join SALESREP Sr on Sr.REP_NUM = Em.EMP_Num
inner join Customer Cu on Cu.REP_NUM = Sr.REP_NUM
group by Sr.REP_NUM, fname, lname
having count(Cu.rep_num) = (select max(AllReps.MostReps) from
(select count(rep_num) As MostReps from Customer group by rep_num) As AllReps)

Thanks in advance.

Upvotes: 0

Views: 99

Answers (2)

fnklstn
fnklstn

Reputation: 492

Ended up using inner join:

select * from
(select Sr.REP_NUM, Fname, Lname, count(cust_num) As TotalCustomers from employee Em
inner join SALESREP Sr on Sr.REP_NUM = Em.EMP_Num
inner join Customer Cu on Cu.REP_NUM = Sr.REP_NUM
group by Sr.REP_NUM, fname, lname) As AllCounts
inner join
(select max(AllCus.MostCusts) As Most from
(select count(cust_num) As MostCusts from Customer group by rep_num) As AllCus) As MaxCusts
on MaxCusts.Most = TotalCustomers

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You can use TOP (1) or TOP (1) WITH TIES. That should work better than the HAVING clause:

select top (1) with ties Sr.REP_NUM, em.Fname, em.Lname, count(*) As TotalCustomers
from employee Em join
     SALESREP Sr
     on Sr.REP_NUM = Em.EMP_Num join
     Customer Cu
     on Cu.REP_NUM = Sr.REP_NUM
group by Sr.REP_NUM, fname, lname
order by count(*) desc;

Upvotes: 1

Related Questions