multiple joins?

[caffrinho]

Please help.

I have two tables,

tbl_teams(
teamid,
team_name,
...
)

and

tbl_fixtures(
fixid,
fix_hometeam,
fix_awayteam,
fix_date
)

fix_hometeam & fix_awayteam correspond to tbl_teams.teamid

What i'm trying to do is run a query that returns the name of the home team, the name of the away team, and the date.
But am failing miserably.

SELECT tbl_teams.team_name, tbl_fixtures.fix_date FROM tbl_fixtures LEFT JOIN tbl_teams ON tbl_fixtures.fix_hometeam=tbl_teams.teamid

Obviously, this only reports the name of the home team & the date. Ive tried putting an extra join in on tbl_fixtures.fix_awayteam=tbl_teams.teamid, but to no avail.

Can anybody help me with this query with the tables as they are, or do i need to re-think my schema?

TIA

 

[hos2]

if a team is always filled in it is not necessarry to use left outer joins

with equal joins it would be like

SELECT away.team_name,home.team_name tbl_fixtures.fix_date FROM tbl_fixtures ,tbl_teams as home, table_teams as away where
fix_hometeam=home.teamid and fix_awayteam=away.teamid


I don't kow the correct syntax with the left outer joins but it is in the same direction

 

[MartijnTonies]

Try:

SELECT away.team_name, home.team_name, tbl_fixtures.fix_date
FROM tbl_fixtures JOIN tbl_teams as home (home.teamid = tbl_fixtures.fix_hometeam) JOIN tbl_teams as away (away.teamid = tnl_fixtures.awayteam)


Martijn Tonies
Database Workbench - the developer tool for InterBase, Firebird, MySQL & MS SQL Server

http://www.upscene.com

 

[caffrinho]

Excellent, just what i was after. I believe both solutions are similar, they both work.

And thanks for the quick response!

 

[MartijnTonies]

Hi,

The difference between the first and the second is that the seconds uses the so-called "explicit"/SQL92 join syntax, whereas the first uses the SQL89 join syntax. With the SQL92 syntax, you can also specify OUTER join if needed

Martijn Tonies
Database Workbench - the developer tool for InterBase, Firebird, MySQL & MS SQL Server

http://www.upscene.com