SQL query for inner join 2 fld on 1 table

Hi

I have two tables tbl_team and tbl_match . Tbl_team stores the name of all the teams in a tournament and tbl_match store the date of match being played between two teams. Here is tables structure

tbl_team (team_id int, team_name text)

tbl_match (match id, team1_id, team2_id, match_date datetime)

Now i need to write a Sql query to display match_id , date and team names playing on that date

Issue here is how do i inner join a foriegn key twice coz team1_id and team2_id both relate to team_id in tbl_team

hope u r getting my point.

Below is sample SQL statments which doesnt work in this siuation .

select * from tbl_match inner join tbl_team on tbl_team.team_id=tbl_match.team_id1 inner join tbl_team on tbl_team.team_id=tbl_match.team_id2

Code (markup):

Thanks 4 u time

Regards
suraj

 

not sure if this is the complete solution, but you'll need a table alias for each reference to tbl_team, as follows:

select * from tbl_match 
  inner join tbl_team team1 on team1.team_id=tbl_match.team_id1 
  inner join tbl_team team2 on team2.team_id=tbl_match.team_id2

Code (markup):