[SQL] Postgres selecting from table row

I have table team which contains columns like this

team_id | name | competition_id
--------+---------+------
1 | name 1 | 10
2 | name 2 | 10


and also i have this table fixtures

fixture_id | home_team_id | away_team_id | competition_id
--------+--------------+--------------+--------------
1 | 1 | 2 | 10
2 | 2 | 1 | 10


Primary key for table fixtures is fixture_id, home_team_id, away_team_id, and competition_id. Also home_team, away_team and competition are foreign keys.

How can I select home_team_name , away_team_name, from fixtures.

Continue reading...