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.

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

Macsicarr wrote:
> 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.
>
I'm having trouble understanding this explanation. I suggest you open your
database in Access, go to the Queries tab and start experimenting with using
the Query Builder to work with your tables. Access has a pretty good online
help system that should help you. You should search for information about
joining tables. Once you have a query built using the GUI designer, switch
to SQL View to see what the sql should look like. Or better yet, save your
query and execute the saved query from asp. See:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

RE: Real SQL scripting quandry linking data to 2 values on the same ro

I think what you are trying to do is a double join to the teams (Players)
tables.

In other words, all teams are in a table and any team can be either a home
team or an away team, correct? The same is true for the players?

SELECT Matches.*, Teams_1.*, Teams.*
FROM (Matches INNER JOIN Teams AS Teams_1 ON Matches.HOMETEAMID =
Teams_1.TEAMID) INNER JOIN Teams ON Matches.AWAYTEAMID = Teams.TEAMID;

I copied this directly from the SQL view in Access so I know it will work.

To do this graphically in Access, add the TEAMS table to your query TWICE.
Then draw a relationship between the HOMETEAMID to one and the AWAYTEAMID to
the other.

HTH,
John





"Macsicarr" wrote:

> 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.
>
>
>
>

RE: Real SQL scripting quandry linking data to 2 values on the same ro

SELECT Matches.*, Teams_1.*, Teams.*
FROM (Matches INNER JOIN Teams AS Teams_1 ON Matches.HOMETEAMID =
Teams_1.TEAMID) INNER JOIN Teams ON Matches.AWAYTEAMID = Teams.TEAMID;

Try that. Basically, you join to the TEAMS table twice: once for the home
team and once for the away team.


"Macsicarr" wrote:

> 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.
>
>
>
>

Re: Real SQL scripting quandry linking data to 2 values on the same ro

Many thanks Guys

Much appreciated.

"John Beschler" wrote in message
news:6C755A44-E624-4C8C-9C55-F72EB8A79FC3@microsoft.com...
SELECT Matches.*, Teams_1.*, Teams.*
FROM (Matches INNER JOIN Teams AS Teams_1 ON Matches.HOMETEAMID =
Teams_1.TEAMID) INNER JOIN Teams ON Matches.AWAYTEAMID = Teams.TEAMID;

Try that. Basically, you join to the TEAMS table twice: once for the home
team and once for the away team.


"Macsicarr" wrote:

> 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.
>
>
>
>