Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2013

    Unanswered: Problem with Search command!

    Hi All,
    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?

  2. #2
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    That's not something worth doing in SQL.

    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).
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts