Sunday, March 11, 2012

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!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

No comments:

Post a Comment