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.
Advertisement
Answer
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