SQL query for inner join 2 fld on 1 table

surajkala · March 25th, 2009, 05:47 AM

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 .

Code:

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

Thanks 4 u time

Regards
suraj

gbianchi · March 25th, 2009, 08:01 AM

Hello.

There is no problem in what you are trying to achieve. To make this work, you have to join two times the same table, giving differents names every time so the database can treat it like they where two differents tables.

Something like:

Code:

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

Something like that will do the trick.

surajkala · March 25th, 2009, 09:38 AM

Thanks a lot @gbianchi

Your sql query worked like a magic.. i was stupid not to think that ways ..

thanks a lot ... once again

regards
Suraj jain