I have hit a bit of a stumbling block with a project I am working on. Basically it tracks football (soccer) results and everything works fine as things are but I there is something else that I hope to do now that I cannot get my head around.
Basically, there are two tables (in this example anyway), with both home_team and away_team in the games table joined to team_id in the teams table...
teams
team_id | team_name
1 | UNITED
2 | CITY
3 | ROVERS
4 | ALBION
games
game_id | home_team | away_team | home_goals | away_goals
1 | 1 | 2 | 1 | 0
2 | 3 | 4 | 1 | 1
3 | 2 | 3 | 2 | 1
4 | 4 | 1 | 0 | 1
5 | 3 | 2 | |
Any row that has a NULL home_goals field is a match that has not yet been played and at the moment everything works fine and the output I have with the join would be
UNITED 1-0 CITY
ROVERS 1-1 ALBION
CITY 2-1 ROVERS
ALBION 0-1 UNITED
ROVERS v CITY
There would be occasions where the outcome of a previous game needs to be decided before both home and away teams are decided. It would be easy enough to implode two team_id's into the same field so would have something like
6 | 1,2 | 4 | |
but I cannot get the tables to join on this so the output would show
UNITED or CITY v ALBION
I thought it would be possible by using
LEFT JOIN teams ON team_id IN home_team
but this just caused empty results.
Is there anyway around this only using the one row? Obviously, one of the team_id's would be removed from the home_team field eventually so I do not want set up several rows for the one games otherwise it may get difficult to find the correct rows to delete when required.
Thanks in advance for any suggestions
Steve