How to get data from a table

I have to select player name, Captain name from below mentioned tabl开发者_运维百科e structure.

table structure are as follows.

create table #temp (Playerid int, PlayerName varchar (100), CaptainId int)

PlayerId     PlayerName     CaptainId
--------------------------------------
1            Dhoni          NULL
2            Sachin         1
3            Rahul          1

I have to select player name along with his captain name from this table. Can anybody

---

there are various ways to get answer of this queries like:

select playerName, (select t2.playername from #temp t2 where t2.playerid = t.captainid  ) from #temp t


select t1.playername , t2.playername captain from #temp t1 left join #temp t2 on t1.captainid = t2.playerid

---

Assuming the CaptainId references the PlayerId on the same table, you would want the following:

select t.PlayerName, t2.PlayerName as CaptainName
from #temp t
left join #temp t2 on t.CaptainId = t2.PlayerId

If you wanted to exclude players with no captain, you would make it an inner join.

You could convert the null, of course:

select t.PlayerName, isnull(t2.PlayerName,'None') as CaptainName
from #temp t
left join #temp t2 on t.CaptainId = t2.PlayerId

---

You can use a self join and table aliases.

Something like

SELECT p.PlayerName,
 c.PlayerName CaptainName
FROM #temp p LEFT JOIN
 #temp c ON p.CaptainId = c.PlayerId

---

SELECT PlayerName, CaptainName 
FROM PlayerTable INNER JOIN CaptainTable
ON PlayerTable.CaptainID=CaptainTable.CaptainID
WHERE CaptainName = 'your query'