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

i getting bit confused basic sql question, hoping give me hint doing wrong:

i have main table different players:

id  | name ----+----- 1   | john 2   | paul 3   | robert 

and table stores matches

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

i trying build query outputs names of both players given date, name of same player being repeated in both fields:

this retrieves nothing:

select date, player_home, name, player_away, name  games, players  date = (date '2012-03-21')  , games.player_home = players.id  , games.player_away = players.id; 

and if remove second and, retrieves same name (john) both players:

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

when name in second "name" field should "paul".

what right way build query?

thanks in advance

you need join players table twice , use different alias names tables

select g.date, p1.name home_name, p2.name away_name games g join players p1 on g.player_home = p1.id  join players p2 on g.player_away = p2.id  g.date = (date '2012-03-21')