How to use Inner Join to get the desired result in SQL Server 2008
I have 2 tables called Players
and Teams
. There are about 100 lines of data.
-
Columns
Players
: Player_ID, Player_Name, Team_ID, Country_ID, Captain_ID, Matches_Played -
Columns
Teams
: Team_ID, Team_Name, Manager_ID, Matches_Won, Matches_Lost, Country_ID
Table Players
:
--------------------------------------------------------------------------
| 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 a player on each team who has played most of the games using the INNER JOIN .
Desired output:
--------------------------------------------------------------------------
| 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 request I was trying to use:
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 entire table with the same value MAX
for Matches_Played
next to each player.
How do I fix the query to get the desired result?
source to share
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;
source to share
You don't need to join at all to do this. Since Sql Server 2005, there is something called an operator APPLY
that might work better for this query:
SELECT p.Player_Name, t.Team_Name, p.Matches_Played
FROM Teams t
CROSS APPLY (
SELECT TOP 1 Player_Name, Matches_Played
FROM Players p
WHERE p.Team_ID = t.Team_ID
ORDER BY Matches_Played DESC
) p
But if this is an assignment where you have to use JOIN for some reason, you need to do it in two steps. First find the number of matches of the target player, and then get the full string for that entry:
SELECT p.Player_Name, t.Team_Name, p.Matches_Played
FROM Teams t
INNER JOIN (
SELECT Team_ID, MAX(Matches_Played) as Max_Played
FROM Players
GROUP BY Team_ID
) played ON played.Team_ID = t.Team_ID
INNER JOIN Players p ON p.Team_ID = played.Team_ID AND p.Matches_Played = played.Max_Played
Note that this may seem like more than one line for each command in the tie case, but the question doesn't really indicate what to do in this situation.
Also note that for both queries I start with the Teams table, not the Players table. The query optimizer should be able to work with it anyway, but I think for this query it makes logical sense for the programmer to start thinking in terms of finding a match for each command record, especially when we see the option APPLY
never use the Players table at the root of the query at all ...
Finally, I suspect there is still a third solution that would use the windowing function (ordered row_number + parition by), which could be even better.
source to share
I think this situation would be a good place to use ROW_NUMBER.
Basic data:
CREATE TABLE #Players (Player_ID INT
,Player_Name VARCHAR(50)
,Team_ID INT
,Country_ID INT
,Captain_ID INT
,Matches_Played INT)
INSERT INTO #Players (Player_ID, Player_Name, Team_ID, Country_ID, Captain_ID, Matches_Played)
VALUES (1, 'Renaldo', 1, 1, 1, 250)
,(2, 'Messi', 2, 2, 2, 220)
,(3, 'Marcelo', 1, 1, 1, 185)
,(4, 'Suarez', 2, 2, 2, 193);
Then I used a basic Select statement and concatenated it with another select statement that uses ROW_NUMBER ()
SELECT p.Player_ID
,p.Player_Name
,p.Team_ID
,p.Country_ID
,p.Captain_ID
,p.Matches_Played
FROM #Players p
INNER JOIN (SELECT Player_ID
,ROW_NUMBER() OVER (PARTITION BY Team_ID ORDER BY Matches_Played DESC) AS rnk
FROM #Players) AS p1 ON p1.Player_ID = p.Player_ID AND rnk = 1
For this, ROW_NUMBER () is used to assign a position to each player in the team. If you had 5 players on the same team, they would count from 1 to 5, where 1 has the most games and 5 has the least. Then, when you join him at rnk = 1, you only join the player with the most games on each team.
If you are confused by the fact that you put the expression in the JOIN, you can do it in other ways.
With CTE (Common Table Expression):
WITH CTE (Player_ID, Rnk) AS
(SELECT Player_ID
,ROW_NUMBER() OVER (PARTITION BY Team_ID ORDER BY Matches_Played DESC)
FROM #Players)
SELECT p.Player_ID
,p.Player_Name
,p.Team_ID
,p.Country_ID
,p.Captain_ID
,p.Matches_Played
FROM #Players p
INNER JOIN CTE ON cte.Player_ID = p.Player_ID AND rnk = 1
With temperature table:
SELECT Player_ID
,ROW_NUMBER() OVER (PARTITION BY Team_ID ORDER BY Matches_Played DESC) AS rnk
INTO #RankTable
FROM #Players
SELECT p.Player_ID
,p.Player_Name
,p.Team_ID
,p.Country_ID
,p.Captain_ID
,p.Matches_Played
FROM #Players p
INNER JOIN #RankTable r ON r.Player_ID = p.Player_ID AND rnk = 1
source to share