1 Reply Latest reply on Jul 30, 2006 6:50 AM by dempster

    Select records from MS Access tables using multiple conditions

    Rustywater
      Hi,

      I am trying to build s select list of team names for a sporting club website. The select list should include teams if the currently logged in user is a coach, team manager, assistant coach, player or a parent of a player.

      When I have only one group of conditions in the where clause it works fine. But that only accounts for one of the above roles. As soon as I ad an OR and another set of criteria my CF7 server's swsoc.exe goes into an permanent loop (well, at least 10 minutes, I reboot after that :-)

      For example, this works fine ...

      <!--- select teams I coach, assistant coach, manage, am a parent of a player, or am a player --->
      <cfquery name="teams" Datasource="#Application.Datasource#">
      SELECT distinct teams.uid_team, team_name, uid_coach, uid_team_manager, uid_coordinator, team_asst
      from teams, player_agegroups, players
      where
      (player_agegroups.uid_team=teams.uid_team and players.uid_user=player_agegroups.uid_user
      and teams.uid_season=#session.season# and players.uid_invoice_to=#session.userid#)
      Order by team_name
      </cfquery>

      But as soon as I add another condition cf7 goes into a loop...

      <!--- select teams I coach, assistant coach, manage, am a parent of a player, or am a player --->
      <cfquery name="teams" Datasource="#Application.Datasource#">
      SELECT distinct teams.uid_team, team_name, uid_coach, uid_team_manager, uid_coordinator, team_asst
      from teams, player_agegroups, players
      where
      (player_agegroups.uid_team=teams.uid_team and players.uid_user=player_agegroups.uid_user
      and teams.uid_season=#session.season# and players.uid_invoice_to=#session.userid#) or
      (player_agegroups.uid_team=teams.uid_team and players.uid_user=player_agegroups.uid_user
      and teams.uid_season=#session.season# and players.uid_user=#session.userid#) or
      (player_agegroups.uid_team=teams.uid_team and players.uid_user=player_agegroups.uid_user
      and teams.uid_season=#session.season# and teams_uid_coach=#session.userid#) or
      (player_agegroups.uid_team=teams.uid_team and players.uid_user=player_agegroups.uid_user
      and teams.uid_season=#session.season# and teams.team_asst=#session.userid#) or
      (player_agegroups.uid_team=teams.uid_team and players.uid_user=player_agegroups.uid_user
      and teams.uid_season=#session.season# and teams.uid_team_manager=#session.userid#)
      Order by team_name
      </cfquery>

      Any combination hangs CF7, whether it be two, three or all conditions, and any combination of conditions..

      The teams table stores team name, coach, team manager, assistant coach and coordinator. Players table stores the player records, and the parent (uid_invoice_to), is part of the player record. player agegroups has the teams, with a record for each player in a team. The whole system is based around seasons, hence the #session.season# variable.

      What should happen is I get a very short list of teams. When I select a team from the select list I then go and grab all the player records from the player_agegroups table joined to the users table to get their names, date of birth etc...

      Can anyone see what I am doing wrong?

      thanks
      Tanya