Get data condition related information from a single table

If you want to display the data with condition based from a single table.

For example: table name: players

sno	name	coach_id
1	Sachin	1
2	Dravid	5
3	Kohli	1
4	Ganguly	2
5	Sehwag	4
6	Gambir	3

Query:

SELECT player.sno, player.name, coach.name AS coachname

FROM players AS coach

JOIN players AS player ON coach.sno = player.coach_id

Output:

sno	name	coach_id
1	Sachin	Sachin
2	Dravid	Sehwag
3	Kohli	Sachin
4	Ganguly	Dravid
5	Sehwag	Ganguly
6	Gambir	Kohli