Real SQL scripting quandry linking data to 2 values on the same row

Hi All
Wonder if you could help me with the problem I've caused myself!!
I have 2 problems both exactly the same and I can't fathom how to get round
them.
PROBLEM 1 (I am using an Access 2000 DB):
I basically poke the codes of team names into a line-by-line history table
so I'm not storing redundant data (ie the long team names) on each row, but
now that I want to query this historic info and link/display the right names
I can't do it. My query is as follows:
select WEEK, HOMETEAMID, HOMETEAMSCORE, HOMETEAMSHOTS,
HOMETEAMSAVES, AWAYTEAMID, AWAYTEAMSCORE, AWAYTEAMSHOTS,
AWAYTEAMSAVES
from MATCHES
where MATCHID='1'
Above is all the fields in my MATCHES table and this query works fine, but
it shows the team ids/codes and I want to link in my TEAMS table so that the
HOMETEAMID and AWAYTEAMIDs are substituted for the actual team names, which
are in my TEAMS table. The TEAMS table simply consists of TEAMID and NAME.
My problem is that I need to get the right individual name for 2 values on
the same row and this is what I don't know how to accomplish in a 'all in
one go' query with the other params.
PROBLEM 2 (I am using an Access 2000 DB):
Again I basically poke the codes of player names into a line-by-line history
table so I'm not storing redundant data (ie the long player names) on each
row, but now that I want to query this historic info and link/display the
right names I can't do it. My query is as follows:
select HOMEPLAYERID, HOMEPLAYERGOAL, HOMEPLAYERASSIST, HOMEPLAYERRATING,
HOMEPLAYERMOM, AWAYPLAYERID, AWAYPLAYERGOAL, AWAYPLAYERASSIST,
AWAYPLAYERRATING, AWAYPLAYERMOM from MATCHLINES
where MATCHID='1'
Above is all the fields in my MATCHLINES table and this query works fine,
but it shows the player ids/codes and I want to link in my PLAYERS table so
that the HOMEPLAYERID and AWAYPLAYERIDs are substituted for the actual
player names, which are in my PLAYERS table. The PLAYERS table simply
consists of PLAYERID and NAME.
Can you help?
Many thanks.Please do not multipost ! ANswered in Programming.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Macsicarr" <nospam@.nospam.com> schrieb im Newsbeitrag
news:uVbGFn1TFHA.3416@.TK2MSFTNGP10.phx.gbl...
> Hi All
> Wonder if you could help me with the problem I've caused myself!!
> I have 2 problems both exactly the same and I can't fathom how to get
> round
> them.
> PROBLEM 1 (I am using an Access 2000 DB):
> I basically poke the codes of team names into a line-by-line history table
> so I'm not storing redundant data (ie the long team names) on each row,
> but
> now that I want to query this historic info and link/display the right
> names
> I can't do it. My query is as follows:
> select WEEK, HOMETEAMID, HOMETEAMSCORE, HOMETEAMSHOTS,
> HOMETEAMSAVES, AWAYTEAMID, AWAYTEAMSCORE, AWAYTEAMSHOTS,
> AWAYTEAMSAVES
> from MATCHES
> where MATCHID='1'
> Above is all the fields in my MATCHES table and this query works fine, but
> it shows the team ids/codes and I want to link in my TEAMS table so that
> the
> HOMETEAMID and AWAYTEAMIDs are substituted for the actual team names,
> which
> are in my TEAMS table. The TEAMS table simply consists of TEAMID and
> NAME.
> My problem is that I need to get the right individual name for 2 values on
> the same row and this is what I don't know how to accomplish in a 'all in
> one go' query with the other params.
> PROBLEM 2 (I am using an Access 2000 DB):
> Again I basically poke the codes of player names into a line-by-line
> history
> table so I'm not storing redundant data (ie the long player names) on each
> row, but now that I want to query this historic info and link/display the
> right names I can't do it. My query is as follows:
> select HOMEPLAYERID, HOMEPLAYERGOAL, HOMEPLAYERASSIST, HOMEPLAYERRATING,
> HOMEPLAYERMOM, AWAYPLAYERID, AWAYPLAYERGOAL, AWAYPLAYERASSIST,
> AWAYPLAYERRATING, AWAYPLAYERMOM from MATCHLINES
> where MATCHID='1'
> Above is all the fields in my MATCHLINES table and this query works fine,
> but it shows the player ids/codes and I want to link in my PLAYERS table
> so
> that the HOMEPLAYERID and AWAYPLAYERIDs are substituted for the actual
> player names, which are in my PLAYERS table. The PLAYERS table simply
> consists of PLAYERID and NAME.
> Can you help?
> Many thanks.
>
>