Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Location
    Wellington, NZ
    Posts
    5

    Question Unanswered: multiple table query returning two different values based on same field name

    Hi,
    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
    FROM
    sport_fixture
    LEFT OUTER JOIN sport_team ON sport_fixture.fixture_hometeamid = sport_team.team_teamid OR sport_fixture.fixture_awayteamid = sport_team.team_teamid
    WHERE
    sport_fixture.fixture_gameid = 1;

    +--------------------+--------------------+----------------+-------------+-------------+
    | fixture_hometeamid | fixture_awayteamid | fixture_gameid | Home | Away |
    +--------------------+--------------------+----------------+-------------+-------------+
    | 3 | 8 | 1 | Highlanders | Highlanders |
    | 3 | 8 | 1 | Reds | Reds |
    +--------------------+--------------------+----------------+-------------+-------------+
    2 rows in set (0.00 sec)

    The result I want is one row where Hoghlanders appear as Home and the Reds as away

    Thanks for any help that can be offered

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    PHP Code:
    select sport_fixture.fixture_gameid
         
    sport_fixture.fixture_hometeamid
         
    home.team_name  as hometeam
         
    sport_fixture.fixture_awayteamid
         
    away.team_name  as awayteam
      from sport_fixture
    left outer 
      join sport_team 
    as home
        on sport_fixture
    .fixture_hometeamid 
         
    home.team_teamid 
    left outer 
      join sport_team 
    as away
        on sport_fixture
    .fixture_awayteamid 
         
    away.team_teamid
     where sport_fixture
    .fixture_gameid 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Location
    Wellington, NZ
    Posts
    5

    Cool

    Brilliant, Cheers.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •