MySQL query joining table data

Question

This is eluding me. I am trying to pull meta data about players that I have matched against each other in a game. Eg: Player 22 vs. Player 54

I have 2 mysql tables.

Table: players (id, name, image_url, etc)

1 | John | john.jpg | etc
2 | George | george.jpg | etc
3 | Ian | ian.jpg | etc
4 | Steve | steve.jpg | etc

Table: matchups (id, left_players_id, right_players_id)

1 | 1 | 3
2 | 2 | 4

I want to display the left player image and name and the right player image and name on my web page.

SELECT m.left_players_id, p.name AS left_player, m.right_players_id, p.name AS right_player FROM players p, matchups m WHERE m.left_players_id = p.id AND m.right_players_id = p.id

0 results because of the AND (requiring a match that doesn't exist)

SELECT m.left_players_id, p.name AS left_player, m.right_players_id, p.name AS right_player FROM players p, matchups m WHERE m.left_players_id = p.id OR m.right_players_id = p.id

4 results (dupes of each row)

What am I missing?

Answer 1

You shouldn't select from multiple tables that way, you should use INNER JOIN, like this:

SELECT lp.name left_name, rp.name right_name
FROM matchups m
INNER JOIN players lp ON lp.id = m.left_players_id
INNER JOIN players rp ON rp.id = m.right_players_id;

That'll do the trick.

Answer 2

Since you are referring to two different players at once, the Player table is needed twice, joined on the Matchups table to define the relation between left and right players.

SELECT l.name, l.image_url, r.name, r.image_url 
FROM Players l 
INNER JOIN matchups m ON l.id=m.left_players_id
INNER JOIN Players r ON r.id=m.right_players_id

Answer 3

You need to join to the players table twice, once for each player.

SELECT m.left_players_id, l.name AS left_player, m.right_players_id, r.name AS right_player 
FROM players r, payers l, matchups m 
WHERE m.left_players_id = l.id AND m.right_players_id = r.id