Help with simple Join 1

[shorinhio]

I have 3 tables: Team, Result, and Fixture

Team contains: TeamId, TeamName
Result contains: FixtureId, TeamId, Goals
Fixture contains: FixtureId, Time

For each fixture:

- one entry is made into the Fixture table with a unique FixtureId

- 2 entries are made into the Result table- each with a seperate TeamId and the same FixtureId

I wish to display a table with header: FixtureId,TeamName1,TeamName2

but so far I have only been able to create a table with header:
FixtureId,TeamName
which for example displays 2 rows for the same FixtureId, each displaying a different team name.

the SQL query used to do this is:

SELECT Fixture.FixtureId, Team.TeamName
FROM Team, Result, Fixture
WHERE Fixture.FixtureId=Result.FixtureId And Team.TeamId=Result.TeamId;

Can anyone please help me with a join query to display a fixture as one row?

Thanks.

 

[lespaul]

Typed, not tested:

SELECT Distinct R.FixtureID, T1.TeamName, T2.TeamName
FROM Result R
INNER JOIN Team T1 on R.TeamID = T1.TeamID
INNER JOIN Team T2 on R.TeamID = T2.TeamID

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[PHV]

Typed, untested:

SELECT F.FixtureId, T1.TeamName AS TeamName1, T2.TeamName AS TeamName2
FROM (((Fixture AS F
INNER JOIN Result AS R1 ON F.FixtureId=R1.FixtureId)
INNER JOIN Team AS T1 ON R1.TeamId=T1.TeamId)
INNER JOIN Result AS R2 ON F.FixtureId=R2.FixtureId)
INNER JOIN Team AS T2 ON R2.TeamId=T2.TeamId
FROM Team, Result, Fixture
WHERE R1.TeamId<R2.TeamId

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shorinhio]

that does the following:

1,Liverpool,Liverpool
1,Chelsea,Chelsea
2,Everton,Everton
2,ManUtd,ManUtd

I want it to be:

1,Liverpool,Chelsea
2,Everton,ManUtd

 

[shorinhio]

thanks PHV I will try that, previous reply was to lespaul

 

[shorinhio]

thanks PHV, that last line helped me work out the solution