Mysql Many to Many Query

I have a many to many table setup in my mysql database. Teams can be in many games and each game has 2 teams. There is a table in between them called teams_games. SHOW CREATE TABLE information follows.

I have been messing with this query for a while. At this point I don't care if it requires sub-queries, joins, or unions. I have tried all of them but nothing has been satisfactory and I think i'm missing something. The disconnect I keep having is finding the two team ids from each game and then using the tid to grab the team information.

What I would like to do is if given a game id (gid) I can query to find:

home_team_name, home_team_id, away_team_name, away_team_id, team_league(away and home will be the same league), all the information from games table

Table Create Table

 teams  CREATE TABLE `teams` (
 `tid` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(60) NOT NULL,
`league` varchar(2) NOT NULL,
`active` tinyint(1) NOT NULL,
PRIMARY KEY (`tid`)

)

CREATE TABLE teams_games (

`tid` int(10) unsigned NOT NULL,
`gid` int(10) unsigned NOT NULL,
`homeoraway` tinyint(1) NOT NULL,
PRIMARY KEY (`tid`,`gid`),
KEY `gid` (`gid`),
CONSTRAINT `teams_games_ibfk_1` FOREIGN KEY (`tid`) REFERENCES `teams` (`tid`),
CONSTRAINT `teams_games_ibfk_2` FOREIGN KEY (`gid`) REFERENCES `games` (`gid`)

)

CREATE TABLE games (

`gid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`location` varchar(60) NOT NULL,
`time` datetime NOT NULL,
`description` varchar(400) NOT NULL,
`error` smallint(2) NOT NULL,
`h开发者_StackOverflowome_score` smallint(2) DEFAULT NULL,
`away_score` smallint(2) DEFAULT NULL,
PRIMARY KEY (`gid`)

)

Why not just drop the teams_games table and alter games:

CREATE TABLE games (

 `gid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`location` varchar(60) NOT NULL,
`time` datetime NOT NULL,
`description` varchar(400) NOT NULL,
`error` smallint(2) NOT NULL,
`home_score` smallint(2) DEFAULT NULL,
`away_score` smallint(2) DEFAULT NULL,
`home_tid` int(10) unsigned NOT NULL,
`away_tid` int(10) unsigned NOT NULL,
PRIMARY KEY (`gid`)
)

Then you can write a simple join like:

SELECT 
    g.*, 
    h.name as home_team, 
    a.name as away_team, 
    h.league as league 
FROM games AS g 
    INNER JOIN teams AS h ON g.home_tid = h.tid
    INNER JOIN teams as a ON g.away_tid = a.tid
WHERE gid = ?

I assumed homeoraway = 1 for home and homeoraway = 0 for away.

SELECT g.*, ht.name, ht.tid, at.name, at.tid, ht.league
FROM games g
JOIN team_games htg ON htg.gid = g.gid AND htg.homeoraway = 1
JOIN team ht ON ht.tid = htg.tid
JOIN team_games atg ON atg.gid = g.gid AND atg.homeoraway = 0
JOIN team at ON at.tid = atg.tid

This works by joining games to team_games for the home team, then to teams for the team info, then doing the same thing for the away team.

I assumed that 1 = home, 2 = away. You can change appropriately.

SELECT
    HT.name AS home_team_name,
    HT.tid AS home_team_id,
    AT.name AS away_team_name,
    AT.tid AS away_team_id,
    HT.league AS team_league
FROM
    teams_games HTG
INNER JOIN teams_games ATG ON
    ATG.gid = HTG.gid AND
    ATG.homeoraway = 2
INNER JOIN teams HT ON
    HT.tid = HTG.tid
INNER JOIN teams AT ON
    AT.tid = ATG.tid
WHERE
    HTG.gid = ???
    HTG.homeoraway = 1

select 
  th.name as home_team_name,
  th.tid as home_team_id,
  ta.name as away_team_name,
  ta.tid as away_team_id,
  th.league as team_league,
  g.* 
from games g
  inner join teams_games tgh on (g.gid = tgh.gid and tgh.homeoraway = <HOME_VALUE>)
  inner join teams_games tga on (g.gid = tga.gid and tga.homeoraway = <AWAY_VALUE>)
  inner join teams th on (tgh.tid = th.tid)
  inner join teams ta on (tga.tid = ta.tid)
where
  g.gid = <GAME_ID>