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...
team_id | team_name
1 | UNITED
2 | CITY
3 | ROVERS
4 | ALBION
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