CODEWITHSUNDEEP
phpsql
Koala
Koala

Reputation: 5523

Using SQL to create football fixtures?

Quite hard to explain but say I have a table named 'Teams' and It contains 5 teams like so:

 team_id, teamname, manager
   1,       Team1,    1
   2,       Team2,    10
   3,       Team3,    3
   4,       Team4,    5
   5,       Team5,    6

I need to create another table looking similar to this (named TeamFixtures for example)

  id, HomeTeam, HomeScore, AwayScore, AwayTeam
                  NULL        NULL

How would I make the HomeTeam and AwayTeam be the team_id of the 'teams' table?

Id need this for each team so they play against each other twice, home and away games.

Is there a Query that could make this quicker for me, rather than typing out the fixtures 1by1

Upvotes: 0

Views: 1818

Answers (1)

jsj
jsj

Reputation: 9381

When an attribute of a relation is they key of another relation, it is called a Foreign Key.

In the case of your TeamFixtures table, you would have two foreign keys hometeam and awayteam.

Your CREATE statement would look like:

CREATE TABLE teamfixtures(
    ...
    hometeam INT,
    awayteam INT,
    ...
    CONSTRAINT fk_hometeam FOREIGN KEY(hometeam) REFERENCES teams, 
    CONSTRAINT fk_awayteam FOREIGN KEY(awayteam) REFERENCES teams,
    CONSTRAINT ck_teams CHECK(hometeam <> awayteam)
    )

Upvotes: 2

Related Questions