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')
Comments
Post a Comment