Clever Geek Handbook

Get value from column depending on another column

From this player table, how would you select the player names along with the names of your captains?

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

As a result, I get the following message:

Player | Captain
================
Jay    | Jeremy
John   | James
James  | James
Jack   | Jeremy
Jeremy | Jeremy
+3


source to share


2 answers


Applying inner join

in 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
+6


source


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

To achieve the desired result, we will use the same table twice, and for this we will use the table alias, the self-join must have aliases.



To get a 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
+1


source






More articles:


All Articles