Mysql Many to Many Query

Answer 1

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 = ?

Answer 2

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>

Answer 3

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

Answer 4

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.