3

From this table of football players, how would you select the players' names alongside their captains' names?

PlayerID | PlayerName | TeamCaptainID
=====================================
1        | Jay        | 5
2        | John       | 3
3        | James      | 3
4        | Jack       | 5
5        | Jeremy     | 5

The result I'm after is:

Player | Captain
================
Jay    | Jeremy
John   | James
James  | James
Jack   | Jeremy
Jeremy | Jeremy
potashin
  • 44,205
  • 11
  • 83
  • 107
Mihai Nagy
  • 177
  • 1
  • 8

2 Answers2

6

Applying inner join on the same table seems to be enough: 

select t1.PlayerName as Player
     , t2.PlayerName as Captain
from tbl t1 
join tbl t2 on t1.TeamCaptainID = t2.PlayerID
potashin
  • 44,205
  • 11
  • 83
  • 107
  • Thank you. You've nailed it. I have also adapted your solution (`outer join` instead of `inner` one) to include empty rows. – Mihai Nagy Apr 28 '15 at 16:00
1

To find the exact result you want, you have to use self-join, it is how this will work:

To achieve the desired outcome, we will use the same table twice and in order to do this we will use the alias of the table, a self join must have aliases.

To get the list of Players and their captain the following sql statement can be used :

SELECT a.PlayerName AS "Player",  
b.TeamCaptainID AS "Captain"
FROM team a, team b  
WHERE a.TeamCaptainID=b.PlayerName
Vivek Gupta
  • 19
  • 4
  • I won't downvote this, but others might because although SQL Server supports that join syntax it is HIGHLY discouraged. Without the where clause you might do an accidental cross join plus that syntax tells you little about the join. And it gets complicated with multiple joins. More discussion here:http://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax – Stephan Apr 28 '15 at 12:57
  • 1
    @Stephan: Thanks, I just tried to resolve his problem, and yes you are right it gets complicated with multiple joins and one must avoid when such situation arises. – Vivek Gupta Apr 28 '15 at 13:07