How to use an Inner Join to get desired result in SQL Server 2008

CrashBandicoot

I have 2 tables called Players and Teams. There are about 100 rows of data.

Players columns: Player_ID, Player_Name, Team_ID, Country_ID, Captain_ID, Matches_Played
Teams columns: Team_ID, Team_Name, Manager_ID, Matches_Won, Matches_Lost, Country_ID

Players table:

  --------------------------------------------------------------------------
  | Player_ID  Player_Name  Team_Id  Country_ID  Captain_ID  Matches_Played|
  -------------------------------------------------------------------------- 
  |    1         Ronaldo       1         1           1            250      |
  |    2         Messi         2         2           2            220      |
  |    3         Marcelo       1         1           1            185      |
  |    4         Suarez        2         2           2            193      |
  --------------------------------------------------------------------------

I want to find the player in each team who has played the most games, using an INNER JOIN.

Desired result:

  --------------------------------------------------------------------------
  | Player_ID  Player_Name  Team_Id  Country_ID  Captain_ID  Matches_Played|
  -------------------------------------------------------------------------- 
  |    1         Ronaldo       1         1           1            250      |
  |    2         Messi         2         2           2            220      |
  --------------------------------------------------------------------------

The query I tried using:

SELECT 
    p.Player_Name, t.Team_Name, src.Matches_Played AS Matches_Played
FROM 
    Players p
INNER JOIN 
    Teams t ON p.Team_ID = t.Team_ID
INNER JOIN 
    (SELECT Team_ID, MAX(Matches_Played) AS Matches_Played
     FROM Players
     GROUP BY Team_ID) src ON t.Team_ID = src.Team_ID
                           AND p.Team_ID = src.Team_ID;

This query returns the whole table, with the same MAX value of Matches_Played next to each player.

How would I go about fixing my query to get the desired result?

etsa

If I understood your question, I think you can try:

SELECT p.Player_Name, t.Team_Name, src.Matches_Played AS Matches_Played
FROM Players p
INNER JOIN Teams t
ON p.Team_ID = t.Team_ID
INNER JOIN (
            SELECT Team_ID, MAX(Matches_Played) AS Matches_Played
            FROM Players
            GROUP BY Team_ID)src
ON p.Team_ID = src.Team_ID
AND p.Matches_Played = src.Matches_Played;

Collected from the Internet

Please contact [email protected] to delete if infringement.