Hello everyone :)
I have a temp table that looks like this (as a result of two seperate inserts into it):
RowNumber starttime endtime
---- ---- ----
1 l 0 l NULL
2 l 360 l NULL
3 l 720 l NULL
4 l 1020 l NULL
5 l 1380 l NULL
6 l NULL l 180
7 l NULL l 540
8 l NULL l 900
9 l NULL l 1200
10 l NULL l 1440
I need some help getting it to look like this (with No NULL cell values)!
RowNumber starttime endtime
---- ---- ----
1 l 0 l 180
2 l 360 l 540
3 l 720 l 900
4 l 1020 l 1200
5 l 1380 l 1440
PLEASE help I have been looking at this for the past 3 days and can't figure it out! :oI just hate RFH problems like this! Oh well...
Has your instructor covered correlated sub-queries yet?
-PatP|||select t1.starttime, t2.endtime
from (select RowNumber, starttime from tbl where endtime is null) t1
inner join (select RowNumber endtime from tbl where starttime is null) t2
on t1.RowNumber = t2.RowNumber - 5|||How do you know which ones belong to which? Does the table look exactly like you just said? After 10 are there any valus with the second column filled and the third column NULL? How did the data get like this in the first place? Are you going to be able to fix that, or will this be ongoing?|||Thank you for your responses! :)
I tried your suggestion rdjabarov:
select t1.starttime, t2.endtime
from (select RowNumber, starttime from #end where endtime is null) t1
inner join (select RowNumber endtime from #end where starttime is null) t2
on t1.RowNumber = t2.RowNumber - 5
and I get an "Invalid column name 'RowNumber'." (RowNumber is a valid column in the temp table).
The temp table became this way because I wanted to store a result set from one query into one column of a temp table, and then store another result set from another query into the second column of the temp table.
The number of result sets for each query will always be the same. For example if I have 14 rows, 1 will link to 8, 2 will link to 9, 3 to 10, etc.
I did try another method: I created one temp table that contained the start times (with RowNumber 1-5), I then created another temp table that contained only the endtimes (with RowNumber 1-5) and joined both tables on the RowNumber and then inserted those values into another temp table (3 temp tables in total!).
I would like to use rdjabarov's solution (with 1 temp table) for improved performance...can you tell me why I am getting the:
"Invalid column name 'RowNumber'." error?
THANK YOU!! :D|||select t1.starttime, t2.endtime
from #end t1
inner join #end t2
on t1.RowNumber = t2.RowNumber - 5
and t2.starttime is null
and t1.endtime is null|||GREAT! That worked! I am indebted to you rdjabarov!!!
Thank you very much for responding as quickly as you did!!! :D
Showing posts with label values. Show all posts
Showing posts with label values. Show all posts
Wednesday, March 7, 2012
Monday, February 20, 2012
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.PROBLEM 1 (I am using an Access 2000 DB):
> select WEEK, HOMETEAMID, HomeTeam.Name,HOMETEAMSCORE, HOMETEAMSHOTS,
> HOMETEAMSAVES, AWAYTEAMID, AwayTeam .Name,AWAYTEAMSCORE,
> AWAYTEAMSHOTS,
> AWAYTEAMSAVES
> from MATCHES
INNER JOIN Teams HomeTeam
ON
Hometeam.TeamID = Matches.HomeTeamID
INNER JOIN Teams AwayTeam
ON
AwayTeam.TeamID = Matches.HomeTeamID
> where MATCHID='1'
Problem 2:
> select HOMEPLAYERID, HomePlayer.Name,HOMEPLAYERGOAL, HOMEPLAYERASSIST,
> HOMEPLAYERRATING,
> HOMEPLAYERMOM, AWAYPLAYERID, AwayPlayer.Name,AWAYPLAYERGOAL,
> AWAYPLAYERASSIST,
> AWAYPLAYERRATING, AWAYPLAYERMOM from MATCHLINES
Inner join PLAYERS HomePlayer
ON PLAYERS.PlayerId = Matchlines.PLAYERSID
INNER JOIN PLAYERS AwayPlayer
ON AwayPlayer.PLAYERSID = MATCHLINES.PLAYERSID
> where MATCHID='1'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Macsicarr" <nospam@.nospam.com> schrieb im Newsbeitrag
news:%23moMEn1TFHA.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.
>|||Hi Jens
Many thanks for the help Jens
Regards
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uKB3Qu1TFHA.2756@.tk2msftngp13.phx.gbl...
PROBLEM 1 (I am using an Access 2000 DB):
> select WEEK, HOMETEAMID, HomeTeam.Name,HOMETEAMSCORE, HOMETEAMSHOTS,
> HOMETEAMSAVES, AWAYTEAMID, AwayTeam .Name,AWAYTEAMSCORE,
> AWAYTEAMSHOTS,
> AWAYTEAMSAVES
> from MATCHES
INNER JOIN Teams HomeTeam
ON
Hometeam.TeamID = Matches.HomeTeamID
INNER JOIN Teams AwayTeam
ON
AwayTeam.TeamID = Matches.HomeTeamID
> where MATCHID='1'
Problem 2:
> select HOMEPLAYERID, HomePlayer.Name,HOMEPLAYERGOAL, HOMEPLAYERASSIST,
> HOMEPLAYERRATING,
> HOMEPLAYERMOM, AWAYPLAYERID, AwayPlayer.Name,AWAYPLAYERGOAL,
> AWAYPLAYERASSIST,
> AWAYPLAYERRATING, AWAYPLAYERMOM from MATCHLINES
Inner join PLAYERS HomePlayer
ON PLAYERS.PlayerId = Matchlines.PLAYERSID
INNER JOIN PLAYERS AwayPlayer
ON AwayPlayer.PLAYERSID = MATCHLINES.PLAYERSID
> where MATCHID='1'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Macsicarr" <nospam@.nospam.com> schrieb im Newsbeitrag
news:%23moMEn1TFHA.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.
>
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.PROBLEM 1 (I am using an Access 2000 DB):
> select WEEK, HOMETEAMID, HomeTeam.Name,HOMETEAMSCORE, HOMETEAMSHOTS,
> HOMETEAMSAVES, AWAYTEAMID, AwayTeam .Name,AWAYTEAMSCORE,
> AWAYTEAMSHOTS,
> AWAYTEAMSAVES
> from MATCHES
INNER JOIN Teams HomeTeam
ON
Hometeam.TeamID = Matches.HomeTeamID
INNER JOIN Teams AwayTeam
ON
AwayTeam.TeamID = Matches.HomeTeamID
> where MATCHID='1'
Problem 2:
> select HOMEPLAYERID, HomePlayer.Name,HOMEPLAYERGOAL, HOMEPLAYERASSIST,
> HOMEPLAYERRATING,
> HOMEPLAYERMOM, AWAYPLAYERID, AwayPlayer.Name,AWAYPLAYERGOAL,
> AWAYPLAYERASSIST,
> AWAYPLAYERRATING, AWAYPLAYERMOM from MATCHLINES
Inner join PLAYERS HomePlayer
ON PLAYERS.PlayerId = Matchlines.PLAYERSID
INNER JOIN PLAYERS AwayPlayer
ON AwayPlayer.PLAYERSID = MATCHLINES.PLAYERSID
> where MATCHID='1'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Macsicarr" <nospam@.nospam.com> schrieb im Newsbeitrag
news:%23moMEn1TFHA.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.
>|||Hi Jens
Many thanks for the help Jens
Regards
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uKB3Qu1TFHA.2756@.tk2msftngp13.phx.gbl...
PROBLEM 1 (I am using an Access 2000 DB):
> select WEEK, HOMETEAMID, HomeTeam.Name,HOMETEAMSCORE, HOMETEAMSHOTS,
> HOMETEAMSAVES, AWAYTEAMID, AwayTeam .Name,AWAYTEAMSCORE,
> AWAYTEAMSHOTS,
> AWAYTEAMSAVES
> from MATCHES
INNER JOIN Teams HomeTeam
ON
Hometeam.TeamID = Matches.HomeTeamID
INNER JOIN Teams AwayTeam
ON
AwayTeam.TeamID = Matches.HomeTeamID
> where MATCHID='1'
Problem 2:
> select HOMEPLAYERID, HomePlayer.Name,HOMEPLAYERGOAL, HOMEPLAYERASSIST,
> HOMEPLAYERRATING,
> HOMEPLAYERMOM, AWAYPLAYERID, AwayPlayer.Name,AWAYPLAYERGOAL,
> AWAYPLAYERASSIST,
> AWAYPLAYERRATING, AWAYPLAYERMOM from MATCHLINES
Inner join PLAYERS HomePlayer
ON PLAYERS.PlayerId = Matchlines.PLAYERSID
INNER JOIN PLAYERS AwayPlayer
ON AwayPlayer.PLAYERSID = MATCHLINES.PLAYERSID
> where MATCHID='1'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Macsicarr" <nospam@.nospam.com> schrieb im Newsbeitrag
news:%23moMEn1TFHA.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.
>
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.
>
>
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.
>
>
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.
>
>
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.
>
>
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.
>
>
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.
>
>
Real beginners question
I have a datagrid that is used to insert values into a table. Beforethe insert occurs I want to be able to check that there are noidentical values already in the table. eg. I am inserting names, so amlooking for last name duplicates only.
If a duplicate is found, it should return an error message to the page.My question is, can I do this duplicate checking via SQL script ie. IFEXISTS etc... and have the error message returned from there, or do Ineed to grab a dataset of the table and look for duplicates before evengoing as far as the database with the new value to be inerted?
If the best approach is to catch the duplicate error after executingthe SQL script, how do I grab the error and return it to the page withthe datagrid? I'm sure this is pretty basic, but I am only just nowgetting into it.
SQL Server 2000 is being used here.
Thanks!
If a duplicate is found, it should return an error message to the page.My question is, can I do this duplicate checking via SQL script ie. IFEXISTS etc... and have the error message returned from there, or do Ineed to grab a dataset of the table and look for duplicates before evengoing as far as the database with the new value to be inerted?
If the best approach is to catch the duplicate error after executingthe SQL script, how do I grab the error and return it to the page withthe datagrid? I'm sure this is pretty basic, but I am only just nowgetting into it.
SQL Server 2000 is being used here.
Thanks!
This was recently handled here, and a link to an article on AspAlliance was provided. It should help you too:
http://aspalliance.com/687
READTEXT and return values
First question :
How can I set the return value of READTEXT to a variable of type nvarchar.
Second question :
I have a table t1 with a ntext column n1.
The ntext column has has words separated by empty space.
Each word can be assumed to be of size <= 255 characters.
How can I extract all the keywords in the ntext column to a table t2 with a column word nvarchar(255).
Assume that the text in column n1 is big enough so that it cannot be cast into a nvarchar or any other simpler type.
Any help on this is greatly appreciated.
Please do provide a sample code.
Alok.Have you referred to books online for READTEXT topoic.
How can I set the return value of READTEXT to a variable of type nvarchar.
Second question :
I have a table t1 with a ntext column n1.
The ntext column has has words separated by empty space.
Each word can be assumed to be of size <= 255 characters.
How can I extract all the keywords in the ntext column to a table t2 with a column word nvarchar(255).
Assume that the text in column n1 is big enough so that it cannot be cast into a nvarchar or any other simpler type.
Any help on this is greatly appreciated.
Please do provide a sample code.
Alok.Have you referred to books online for READTEXT topoic.
Subscribe to:
Posts (Atom)