I have a database of games at GameSecretary.Com. Some of these games are related, for example - Dr. Dodgeball, Regular Dodgeball, and Freeball.
I also have a weekly game generator that selects the best games for your group to play. The problem is that right now it is sometimes generating related games (e.g., Dr. Dodgeball and Regular Dodgeball) and usually you want to avoid using two of the same type of games on the same night.
So I decided to create a GameRelationships table. This table consists of a unique identifier, a GameID and a RelatedID. The first identifies the game we're talking about, the other identifies the game that is related to it. So it might look something like this:
1 - Dodgeball - Tag Dodgeball
Except that everything is numeric (a unique key).
Anyways, what I want to do is exclude results if they are related. Okay, thats simple. But what if I want to keep the first game that comes up that has a relation, but not any further games? For example, its fine for someone to have Dr. Dodgeball on their list of weekly games, I just don't want the script automatically generating Dr. Dodgeball and Freeball on the same list (or any other variation of dodgeball).
I've searched everywhere and have spent several hours today trying to crack this problem, but haven't been able to figure it out.
Right now I am attempting to put one result in a #results table. Then add additional rows. The idea being that I could create a insert statement that said (pseudo):
Insert into #results where the #results.GameRelated != the Game.ID.
Basically, what all that says is "Add a row to the temporary table if the already existing games aren't related to this game, if they are related, try the next record."
Of course if this all worked I wouldn't be posting here. Your thoughts and solutions would be appreciated.
David.
-------------
- http://www.davemackey.net/" rel="nofollow - Dave Mackey - Virtual Home.
|