I'm designing a simple website on ASP & Access, I have a problem when using search method to display the players with their current teams.
Databases as below:
P_ID,P_Name,.....etc (Player Info with P_ID as Primary Key)
T_ID,T_Name,....etc (Team Info with T_ID as Primary Key)
TP_ID,P_ID,T_ID,TP_DateJoined,TP_DateLeft,TP_Jerse yNo,...etc (With TP_ID as primary Key, while P_ID,T_ID as Foreign keys)
In the teams database there are clubs & National teams as well, so a player might be at 2 teams in the same time.
I wrote this sql statement to search for a player :
Player = Request.Form("Player")
"select * FROM Teams INNER JOIN (Players INNER JOIN
Team_Player ON Players.P_ID = Team_Player.P_ID)
ON Teams.TEAM_ID = Team_Player.Team_ID WHERE P_Name Like '%"&Player&"%'
ORDER BY P_Name,Players.P_ID desc"
the problem is when i click the search button, if there is someone who plays for his national team the name will be duplicated in two rows one with his current club and another with national team.
for ex. when i search for "Messi" the results appears as below:
L. Messi - Photo - Forward - 24/June/1987 - FC Barcelona
L. Messi - Photo - Forward - 24/June/1987 - Argentina
what i need is to display is all players info in one row and put the current teams together like :
L. Messi - Photo - Forward - 24/June/1987 - FC Barcelona / Argentina
do i have to write another statement to display the current teams?
I suggest pulling down the recordset from the database as you're doing now.
You already have them ordered by name and ID. Walk through the records one by one and keep track of the values from the "last" record. If the values you care about match, concatenate the team together. If the values are different, you have a new player and you can start building a new result for display.
If you're using ASP.NET 3.5+ then you have LINQ, which makes this easier (or at least requires less code).