Joining tables on an IN clause

[MargateSteve]

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

[smantscheff]

This structure of games depending on the outcome of previous games is effectively a recursive hierarchy or tree structure. This cannot be properly displayed in plain queries or views. You will need a function which computes for a given match recursively the outcomes of all previous matches to determine the teams for this match.