Unanswered: multiple table query returning two different values based on same field name
Have struck a dead end on a result I want to do with only one query.
Basically there are two tables Fixture and Team.
Fixture has results of a sporting fixture where the two teams are represented by:
fixture_hometeamid and fixture_awayteamid and the primary key of fixture_gameid
Team has the details of sports teams and the primary key of
team_teamid with the field name team_name that I am trying to display in the result of the query twice basaed on the hometeamid and awayteamid.
Here is the query and the result. Sorry for the crap explanation but I don't really know how to explain it.
SELECT sport_fixture.fixture_hometeamid,sport_fixture.fix ture_awayteamid, sport_fixture.fixture_gameid, sport_team.team_name as Home, sport_team.team_name as Away
LEFT OUTER JOIN sport_team ON sport_fixture.fixture_hometeamid = sport_team.team_teamid OR sport_fixture.fixture_awayteamid = sport_team.team_teamid
sport_fixture.fixture_gameid = 1;
, home.team_name as hometeam
, away.team_name as awayteam
join sport_team as home
join sport_team as away
where sport_fixture.fixture_gameid = 1