T
Teaser
Guest
This refers to a Q2 league site.
I have 2 tables- 'results' and 'teams'. Strangely enough, the results table contains all the games, and the teams table contains all the teams in the league.
In the results table there are two columns (amongst others) called 'team1' and 'team2'. Originally these two columns heald the names of the two teams who played. I wanted to make sure a result couldn't be added with a team that isn't in the league, so I set up these two columns (team1 and team2) as foreign keys, with the id column in the teams table as the reference.
Each team in the teams table has a unique id number (id col. is auto_increment). Id column is a primary key of teams table.
So when you add a result team1 and team2 columns have the id value of the team from the teams table (oppose to the actual name of the team).
So, when it came to displaying games, I attempted to use the join table method. I wanted it to return one row for each game - but no matter what I did, it just bloody wouldn't. Here is the relevant information:
Teams table
------------
id | name
------------
Results table
---------------------------------------------------
num | team1* | team2* | score1 | score2
---------------------------------------------------
* - foreign keys, with teams.id as reference.
num - number of each result.
This is what I wanted it to return:
----------------------------------------------------------------------
num | team1 | team2 | score1 | score2 | name | name
----------------------------------------------------------------------
Where the name columns have the actual names of the teams who played, taken from the name column in the teams table.
It either returned stupid rows (eg, Scotland v Scotland), or returned more than one row - not what I wanted.
So I decided to copy my teams table (then rename the copy) and when doing the join take the team2 name from this table. This worked fine, but it caused some awkward problems later on, and I doubted this was the correct way to do it. I gave using just the teams table another go, but got nowhere.
So now I'm just looking up the name of the team in the teams table based on the number in the team1 and team2 columns in the results table. It's turned out to be a bit easier, and possibly a bit quicker as there are less queries involved in other parts of the site.
I'd have included the join statements I tried, but I didn't think it was necessary.
In short, does mySQL not like two foreign key columns (team1 and team2) in the same table (results) using the same reference(id in teams table) ? Or was I doing something wrong ?
