Joined two seperate fields, which now have same name: need to seperate 1

[ElBlues]

Hi
I have created a recordset from two tables, teams and fixres. teams has team data (ID, name etc) fixres has two values one for each of the teams in the fixture (Hometeam_ID, Awayteam_ID). I have managed to join the two tables to match the ID with the team name, however I can only get one team value!

Here is the joining query:
SELECT ID, TeamName
FROM mofc_fixres INNER JOIN mofc_teams ON mofc_fixres.Hometeam_ID=mofc_teams.Team_ID AND mofc_fixres.Awayteam_ID=mofc_teams.Team_ID

How can I create two values instead of one?

 

[PHV]

Something like this ?
SELECT ID, TeamName
FROM mofc_fixres INNER JOIN mofc_teams ON mofc_fixres.Hometeam_ID=mofc_teams.Team_ID OR mofc_fixres.Awayteam_ID=mofc_teams.Team_ID
Or like this ?
SELECT ID, H.TeamName As HomeTeam, A.TeamName As AwayTeam
FROM (mofc_fixres
INNER JOIN mofc_teams As H ON mofc_fixres.Hometeam_ID = H.Team_ID)
INNER JOIN mofc_teams As A ON mofc_fixres.Awayteam_ID = A.Team_ID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ElBlues]

Yes!! The second one works a treat... Now for my next dilemma, how to match up the Referee Number to the Referee_ID in the Ref table???

Here is the query so far:

SELECT ID, H.TeamName As HomeTeam, A.TeamName As AwayTeam, Referee
FROM (mofc_fixres INNER JOIN mofc_teams As H ON mofc_fixres.Team_ID = H.Team_ID) INNER JOIN mofc_teams As A ON mofc_fixres.Awayteam_ID = A.Team_ID

Can I add another INNER JOIN after A.Team_ID to give me the ref join??

 

[ElBlues]

S'OK... Cracked it!!!!

SELECT mofc_fixres.ID, H.TeamName As HomeTeam, A.TeamName As AwayTeam, RefName
FROM ((mofc_fixres INNER JOIN mofc_teams As H ON mofc_fixres.Team_ID = H.Team_ID) INNER JOIN mofc_teams As A ON mofc_fixres.Awayteam_ID = A.Team_ID) INNER JOIN mofc_ref ON mofc_fixres.Referee=mofc_ref.ID