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.

You can join the fixtures table on the teams table twice - one for the home team and one for the away team:

SELECT  fixture_id, home.name, away.name
FROM    fixtures f
JOIN    teams home on home.team_id = f.home_team_id
JOIN    teams away on away.team_id = f.way_team_id