Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Posts
    31

    Unanswered: Double join... kind of...

    I'm having problem getting the output i want.

    I have a table looking like this (tbl_team)

    team_id | team_name
    1 | TEAM1
    2 | TEAM2
    3 | TEAM3
    ...

    and another one like this (tbl_match)

    match_id | home_team | away_team | score
    1 | 1 | 3 | 3-1
    2 | 2 | 3 | 0-0
    3 | 2 | 1 | 1-5
    ...

    I am trying to form a question looking like this:

    home team | away team | score
    TEAM1 | TEAM3 | 3-1
    TEAM2 | TEAM3 | 0-0
    TEAM2 | TEAM1 | 1-5
    ...

    can anyone help me with this? It's easy to make this output in my programming enviroment (VS.NET) but I would really like for a sql-statement to have this output.

    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select h.team_name as hometeam
         , a.team_name as awayteam
         , score
      from tbl_match
    inner
      join tbl_team as h
        on home_team = h.team_id  
    inner
      join tbl_team as a
        on away_team = a.team_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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