CODEWITHSUNDEEP
mysqlmariadb
Tihomir Mitkov
Tihomir Mitkov

Reputation: 846

Same foreign key twice in a table

I have a database with soccer matches. The table that holds matches use foreign keys to store teams (team1 and team2 - host and guest, respectively). I need to join results with those from the team table.

match:

+-----------------+------------------+------+-----+---------+----------------+
| Field           | Type             | Null | Key | Default | Extra          |
+-----------------+------------------+------+-----+---------+----------------+
| match_id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| championship_id | int(10) unsigned | YES  | MUL | NULL    |                |
| team1_id        | int(10) unsigned | YES  | MUL | NULL    |                |
| team2_id        | int(10) unsigned | YES  | MUL | NULL    |                |
| factor_1        | decimal(4,2)     | YES  |     | NULL    |                |
| factor_x        | decimal(4,2)     | YES  |     | NULL    |                |
| factor_2        | decimal(4,2)     | YES  |     | NULL    |                |
| goals_team_1    | tinyint(4)       | YES  |     | NULL    |                |
| goals_team_2    | tinyint(4)       | YES  |     | NULL    |                |
| match_date      | date             | YES  |     | NULL    |                |
+-----------------+------------------+------+-----+---------+----------------+

team:

+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| team_id | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name    | varchar(32)      | YES  |     | NULL    |                |
+---------+------------------+------+-----+---------+----------------+

My guess was to use this query, but it is invalid:

SELECT match.match_id, team.name, team.name, match.match_date
FROM `match`
INNER JOIN `team`
ON match.team1_id=team.team_id,
match.team2_id=team.team_id

Upvotes: 2

Views: 4234

Answers (1)

jpw
jpw

Reputation: 44881

You have to join the team table twice and use aliases to distinguish each join, like this:

SELECT match.match_id, team1.name, team2.name, match.match_date
FROM `match`
INNER JOIN `team` as team1 ON match.team1_id=team1.team_id
INNER JOIN `team` as team2 ON match.team2_id=team2.team_id

Upvotes: 3

Related Questions