Hey guys I'm having a bit of a problem with a database application i'm trying to design for organizing information from a non profit childrens hockey organization i'm with. Right now my problem is with 3 tables. I've got the application querying a list of games. And now i need it to remove any games from the list where the OFFICIALS(rank)>=OFFICIATING_LEVELS(ref_rank or lines_rank) and i have to use the level, division and sex columns from GAMES, and OFFICIATING_LEVELS to compare them. I've typed out my tables below so you can get a better idea of what i mean.
Basically it looks to me like with this setup i have to take level, division, and sex, from GAMES and compare it with the same in OFFICIATING_LEVELS. Take the ref_ranks and lines_ranks from there and compare it with rank from OFFICIALS. And filter the games accordingly. Now weather that's possible or not is a whole other issue, hehe. Also i was wondering weather it'd be better to do this filtering with MySQL queries, or let the PHP do it?
Any help would be great, and if this query can't be done, maybe some suggestions on reorganizing the tables. The main thing with the tables is the GAMES table will be filled by communities who don't use our ranking schedule, so i'm trying to get around having rank columns that I'd have to fill in myself.
OFFICIALS
ref_id first_name last_name rank birth_date address city postal_code squad_zone home_arena phone email mileage last_game
GAMES
game_id arena_code level division sex start_time ice_time date num_officials official1_id official2_id official3_id
OFFICIATING_LEVELS
level division sex ref_rank lines_rank min_age rate_id
Thanks so much guys,
Lloyd