hockey pool picks

[Rob Cotnam]

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?

[Chris Thibodeaux]

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?