CODEWITHSUNDEEP
mysql
Hmorv
Hmorv

Reputation: 146

Trying to return values from another table

I have two MYSQL tables for card games management:

The first one, Matches table:

ID   ATeam BTeam Player1 Player2
001  T1    T2    1       2

there's another table (Players table) with player's ID and names:

ID Name
1  Dave
2  Martin

What I'm trying to do is with one MYSQL SELECT get all matches where player1 (for instance ID player=1) is currently appointed to, and return player's name, not the ID. In order to achieve that, I need to query players table but I'm not sure what to use: INNER JOIN perhaps??

The result should be:

001  T1  T2  Dave  Martin

Instead of

001 T1  T2  1  2

Any idea?

Thanks!

Upvotes: 0

Views: 29

Answers (3)

Sanooj T
Sanooj T

Reputation: 1327

Same as @Giorgos Betsos answer but use left join

SELECT Matches.ID, Matches.ATeam, Matches.BTeam, p1.Name, p2.Name
FROM Matches
LEFT JOIN Players AS p1 ON (Matches.Player1 = p1.ID)
LEFT JOIN Players AS p2 ON (Matches.Player2 = p2.ID)

Upvotes: 1

mike
mike

Reputation: 421

select m.id, m.ATeam, m.BTeam, p1.name, p2.name from matches m inner join player p1 inner join player p2 on m.player1 = p1.id and m.player2 = p2.id;

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

That a simple join query:

SELECT m.ID, m.ATeam, m.BTeam, p1.Name, p2.Name
FROM Matches AS m
JOIN Players AS p1 ON m.Player1 = p1.ID
JOIN Players AS p2 ON m.Player2 = p2.ID
WHERE m.Player1 = ?

Upvotes: 2

Related Questions