Postgres selecting from table row

Question

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.

How can I select home_team_name, away_team_name, using join or where clause ? — Jurica Penjgušić
– Jurica Penjgušić, Commented Sep 23, 2015 at 17:12

Mureinik · Accepted Answer · 2015-09-23 17:13:08Z

3

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

answered Sep 23, 2015 at 17:13

Mureinik

316k54 gold badges402 silver badges406 bronze badges

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

Collectives™ on Stack Overflow

Postgres selecting from table row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related