Slecting records from MSAccess tables using multiple conditions

hi,

i trying build s select list of team names sporting club website. select list should include teams if logged in user coach, team manager, assistant coach, player or parent of player.

when have 1 group of conditions in clause works fine. accounts 1 of above roles. ad or , set of criteria cf7 server's swsoc.exe goes permanent loop (well, @ least 10 minutes, reboot after :-)

for example, works fine ...

<!--- select teams coach, assistant coach, manage, parent of player, or 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 , players.uid_user=player_agegroups.uid_user
and teams.uid_season=#session.season# , players.uid_invoice_to=#session.userid#)
order team_name
</cfquery>

but add condition cf7 goes loop...

<!--- select teams coach, assistant coach, manage, parent of player, or 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 , players.uid_user=player_agegroups.uid_user
and teams.uid_season=#session.season# , players.uid_invoice_to=#session.userid#) or
(player_agegroups.uid_team=teams.uid_team , players.uid_user=player_agegroups.uid_user
and teams.uid_season=#session.season# , players.uid_user=#session.userid#) or
(player_agegroups.uid_team=teams.uid_team , players.uid_user=player_agegroups.uid_user
and teams.uid_season=#session.season# , teams_uid_coach=#session.userid#) or
(player_agegroups.uid_team=teams.uid_team , players.uid_user=player_agegroups.uid_user
and teams.uid_season=#session.season# , teams.team_asst=#session.userid#) or
(player_agegroups.uid_team=teams.uid_team , players.uid_user=player_agegroups.uid_user
and teams.uid_season=#session.season# , teams.uid_team_manager=#session.userid#)
order team_name
</cfquery>

any combination hangs cf7, whether two, 3 or conditions, , combination of conditions..

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

what should happen short list of teams. when select team select list go , grab player records player_agegroups table joined users table names, date of birth etc...

can see doing wrong?

thanks
tanya

rustywater wrote:
>
> example, works fine ...
>
> <!--- select teams coach, assistant coach, manage, parent of player,
> or player --->
> <cfquery name="teams" datasource="#application.datasource#">
> select distinct teams.uid_team, team_name, uid_coach, uid_team_manager,
> uid_coordinator, team_asst
> teams, player_agegroups, players
>
> (player_agegroups.uid_team=teams.uid_team ,
> players.uid_user=player_agegroups.uid_user
> , teams.uid_season=#session.season# ,
> players.uid_invoice_to=#session.userid#)
> order team_name
> </cfquery>
>
> add condition cf7 goes loop...
>
> <!--- select teams coach, assistant coach, manage, parent of player,
> or player --->
> <cfquery name="teams" datasource="#application.datasource#">
> select distinct teams.uid_team, team_name, uid_coach, uid_team_manager,
> uid_coordinator, team_asst
> teams, player_agegroups, players
>
> (player_agegroups.uid_team=teams.uid_team ,
> players.uid_user=player_agegroups.uid_user
> , teams.uid_season=#session.season# ,
> players.uid_invoice_to=#session.userid#) or
> (player_agegroups.uid_team=teams.uid_team ,
> players.uid_user=player_agegroups.uid_user
> , teams.uid_season=#session.season# , players.uid_user=#session.userid#) or
> (player_agegroups.uid_team=teams.uid_team ,
> players.uid_user=player_agegroups.uid_user
> , teams.uid_season=#session.season# , teams_uid_coach=#session.userid#) or
> (player_agegroups.uid_team=teams.uid_team ,
> players.uid_user=player_agegroups.uid_user
> , teams.uid_season=#session.season# , teams.team_asst=#session.userid#) or
> (player_agegroups.uid_team=teams.uid_team ,
> players.uid_user=player_agegroups.uid_user
> , teams.uid_season=#session.season# ,
> teams.uid_team_manager=#session.userid#)
> order team_name
> </cfquery>

you not or'ing data filter, or'ing
relations between tables. should define relations between
tables in explicit join statements in from, , filter data in
your where. should remove duplice filters in
statement. like:

select distinct
teams.uid_team,
team_name,
uid_coach,
uid_team_manager,
uid_coordinator,
team_asst
from
(teams inner join
player_agegroups on player_agegroups.uid_team=teams.uid_team)
inner join players on players.uid_user=player_agegroups.uid_user
where
teams.uid_season=#session.season#
,
(
players.uid_invoice_to=#session.userid#
or
players.uid_user=#session.userid#
or
teams_uid_coach=#session.userid#
or
teams.team_asst=#session.userid#
or
teams.uid_team_manager=#session.userid#
)
order
team_name

jochem

--
jochem van dieten
adobe community expert coldfusion

More discussions in Database Access

adobe