Reputation: 45
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?
Upvotes: 0
Views: 113
Reputation: 5776
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.
Upvotes: 0
Reputation: 57787
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
Upvotes: 0
Reputation: 45002
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
Upvotes: 1