a join with two keys relating to same table

[PeterW]

Hi,
I have a table TEAMS and a table FIXTURES. I'd like to display a list of fixtures (xxx vs. yyyy) but can't work out how to reference the two teams. Can anyone help?

at the moment I am using two parallel queries
SELECT * FROM FIXTURES, TEAMS WHERE
TEAMS.teamId = FIXTURES.challengingTeamId
and
SELECT * FROM FIXTURES, TEAMS WHERE
TEAMS.teamId = FIXTURES.challengedTeamId
but wondered if I could get it in one query?

thanks, Peter

[lilleman]

Hi,

I think that something like the query below should give you the desired result.

SELECT ta.teamId, tb.teamId FROM fixtures AS f
  INNER JOIN teams AS ta ON ta.teamId = f.challengingTeamId
  INNER JOIN teams AS tb ON tb.teamId = f.challengedTeamId

Yours, Erik.

[PeterW]

thanks that was just what I was looking for