How to write a SQL Query for the following

Question

I have 3 tables Teams, Players and TxP

Teams table has the columns TeamName and TeamID(Primary Key) Players table has the columns PlayerName and PlayerID(Primary Key) TxP table has the columns PlayerID, TeamID

I would like to write a query to get the result set as PlayerName, TeamName

Answer 1

select Teams.TeamName, TxP.PlayerID
from Teams
right outer join TxP on TxP.TeamID = Teams.TeamID

Answer 2

SELECT Players.PlayerName, Teams.TeamName FROM Players, Teams, TxP
WHERE Teams.TeamID = TxP.TeamID AND Players.PlayerID = TxP.PlayerID

Answer 3

SELECT A.PlayerName, B.TeamName FROM Players A, Teams B, TxP C WHERE A.PlayerID=C.PlayerID AND B.TeamID=C.TeamID

This query only shows players asigned to at least one team and teams with at least one player

Answer 4

Select Player.PlayerName,Team.TeamName from Player,Team,TXP where Team.TeamId=TXP.TeamId and Player.PlayerId=TXP.PlayerId

Answer 5

SELECT Players.PlayerName, Teams.TeamName
FROM Players
LEFT JOIN TxP ON Players.PlayerID = TxP.PlayerID
LEFT JOIN Teams ON TxP.TeamID = Teams.TeamID
ORDER BY Players.PlayerName

That will give you a row for every player and team combination, including a row with empty TeamName if the player does not have a team.

To only show players that have teams just switch to not using left joins.

---

For example this might give:

Bob      Sample United
Bob      Some other team
Chris

If you use normal (inner) joins you won't get the Chris result. If a player can have multiple teams but you only want a single result you'll need a GROUP BY and an aggregate function to group up your team names into a single value.