Nikheel Rajman Nikheel Rajman - 3 months ago 28
SQL Question

SQL joins in MS SQL Server 2014

I have 2 tables, EPISODES and PRESENTERS

EPISODES
EPI_NO PRES_ID
1 001
2 002
3 001

PRESENTERS
PRES_ID FNAME SNAME
001 Tom Smith
002 Steve Harris


I want to write a query that finds the Presenter who has done the most number of episodes and display his FNAME and SNAME is this format:

Tom Smith 2

Select Top 1 PRES_ID,count(*) as NumberOfEpisodes From
EPISODES
Group By PRES_ID
Order By NumberOfEpisodes DESC


I have managed to make this query that finds the Presenter that has done the most number of episodes.

Thanks!

Answer

Now JOIN it with other table to get rest data like

select p.FNAME + ' ' + p.SNAME as Name,
xxx.NumberOfEpisodes
FROM PRESENTERS p
JOIN ( Select Top 1 PRES_ID,
       count(*) as NumberOfEpisodes 
       From EPISODES    
       Group By PRES_ID ) xxx ON p.PRES_ID = xxx.PRES_ID;