How to build an SQL query that directs two different columns to the same reference column

Question

I am getting a bit confused with this basic SQL question, I was hoping you could give me a hint about what am I doing wrong:

I have a main table with different players:

id  | name
----+-----
1   | John
2   | Paul
3   | Robert

And another table that stores their matches

date      |  player_home  |  player_away
----------+---------------+-------------
2012-03-21|  1            |  2
2012-04-10|  2            |  3

I am trying to build a query that outputs the names of both players for a given date, but the name of the same player is being repeated in both fields:

This retrieves nothing:

SELECT date, player_home, name, player_away, name 
FROM games, players 
WHERE date = (DATE '2012-03-21') 
AND games.player_home = players.id 
AND games.player_away = players.id;

And if I remove the second AND, it retrieves the same name (John) for both players:

date      |  player_home  |  name   |   player_away  |   name
----------+---------------+---------+----------------+-------
2012-03-21|  1            |  John   |   2            |   John

When the name in the second "name" field should be "Paul".

What is the right way to build this query?

Thanks in advance

juergen d · Accepted Answer · 2015-05-31 11:00:32Z

1

You need to join the players table twice and use different alias names for the tables

SELECT g.date, p1.name as home_name, p2.name as away_name
FROM games g
JOIN players p1 ON g.player_home = p1.id 
JOIN players p2 ON g.player_away = p2.id 
WHERE g.date = (DATE '2012-03-21')

answered May 31, 2015 at 11:00

juergen d

205k40 gold badges305 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

telex-wap Over a year ago

Excellent, I was guessing it had to be something with join... I also didn't know about the alias thing. Useful to know!

Houari · Accepted Answer · 2015-05-31 11:01:03Z

1

SELECT date, player_home, players_home.name, player_away, player_away.name 
FROM games
left outer join players players_home on player_home=players.id
left outer join players players_away on player_away=players.id
WHERE date = (DATE '2012-03-21') ;

answered May 31, 2015 at 11:01

Houari

5,6513 gold badges33 silver badges57 bronze badges

Collectives™ on Stack Overflow

How to build an SQL query that directs two different columns to the same reference column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related