-
hockey pool picks
I'm trying to build an ASP hockey pool application and things have been going well until now. Before I go into detail, let me show you a pared down model of my two-table Access97 database:
TableEntrant has the following fields:
entrantID
name
phone
pick1
pick2
pick3
pick4
pick5...
pick10
TablePlayers has the following fields:
playerID
playerName
teamCity
teamNickname
goals
assists
gamesPlayed
The pool works like this: a contestant enters their personal information and chooses 1 player from each of 9 boxes with five players to choose from, and 1 player from a box of 10 players to choose from (10 picks total, from a master list of 55 players). When the entry is created, the player1, player2, player3 etc. fields are populated with a number between 1 and 55 corresponding to the NHL player in the players table. Fine.
My problem is joining the two tables for output in one SQL statement: there is no relationship between them. I can confirm an entrant's NUMERICAL picks, but I can't relate to the name, goals, assists, etc. of the picks.
Can anyone help? I suspect I need a junction table of some sort, but how?
-
hockey pool picks (reply)
Create an associative entity as follows:
"TABLEEntrantPlayer"
EntrantID int PRIMARY KEY
PlayerID int PRIMARY KEY
-------------
CreateDate Default GETDATE() or NOW()
EntrantID References TableEntrant.EntrantID
PlayerID References TablePlayers.PlayerID
------------
Rob Cotnam at 9/4/01 9:09:30 AM
I'm trying to build an ASP hockey pool application and things have been going well until now. Before I go into detail, let me show you a pared down model of my two-table Access97 database:
TableEntrant has the following fields:
entrantID
name
phone
pick1
pick2
pick3
pick4
pick5...
pick10
TablePlayers has the following fields:
playerID
playerName
teamCity
teamNickname
goals
assists
gamesPlayed
The pool works like this: a contestant enters their personal information and chooses 1 player from each of 9 boxes with five players to choose from, and 1 player from a box of 10 players to choose from (10 picks total, from a master list of 55 players). When the entry is created, the player1, player2, player3 etc. fields are populated with a number between 1 and 55 corresponding to the NHL player in the players table. Fine.
My problem is joining the two tables for output in one SQL statement: there is no relationship between them. I can confirm an entrant's NUMERICAL picks, but I can't relate to the name, goals, assists, etc. of the picks.
Can anyone help? I suspect I need a junction table of some sort, but how?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|