Compare tables

[ssanfu]

@ ArviLaanemets,


Good catch, thanks. I must of looked at that 10 times and still missed it ..... it was at 1:00 am.
The perils of being tired and copy and pasting.

Yes, it should have been

jnctPlayersGames
-----------------
PlayerGameID_PK Autonumber
PlayerID_FK Number - Long Integer (Link to Player table)
GameID_FK Number - Long Integer (Link to Games table)
(other fields as desired)

[Svenne]

@ ArviLaanemets,
Good catch, thanks. I must of looked at that 10 times and still missed it ..... it was at 1:00 am.
The perils of being tired and copy and pasting.

Yes, it should have been

jnctPlayersGames
-----------------
PlayerGameID_PK Autonumber
PlayerID_FK Number - Long Integer (Link to Player table)
GameID_FK Number - Long Integer (Link to Games table)
(other fields as desired)

Thanks all of you. But I am not sure I understand. If I have the following fields for the players: FirstName, LastName and BirthYear; how could the programme then know what persons who practises both sports?

[orange]

@Svenne

Every table in a relational database should have a Primary Key. The purpose of the PK is to uniquely identify records in that table.

The PlayerPK identifies each Player. The GameID (or SportID) identifies each Game/Sport.
These are separate things , so separate tables.
Your requirement is to show which Player, participates in which Game (I call it Sport). This requires a junction table, since a player may participate in more than 1 Game/sport and a Game/Sport could be the choice of many Players.

Consider
Players
PlayerId- PlayerName
1--Jim
2-John
3-Sam

Games/sport
1-Baseball
2-Skiing
3-Hockey
4-Soccer

Junction PlayerParticipatesIN

John participates in Hockey and Soccer
Sam participates in Soccer
Jim participates in Baseball, Skiing and Soccer

the junction table

PlayerID--------GameID
1.......................1===========Jim Baseball
1.......................2===========Jim Skiing
1.......................4===========Jim Soccer
2.......................3===========John Hockey
2.......................4===========John Soccer
3.......................4===========Sam Soccer

[Svenne]

Thanks for all the replies. One final question. In the jnct (other fields as desired). I am not sure I understand. What other fields?

Again many thanks.

@Svenne

Every table in a relational database should have a Primary Key. The purpose of the PK is to uniquely identify records in that table.

The PlayerPK identifies each Player. The GameID (or SportID) identifies each Game/Sport.
These are separate things , so separate tables.
Your requirement is to show which Player, participates in which Game (I call it Sport). This requires a junction table, since a player may participate in more than 1 Game/sport and a Game/Sport could be the choice of many Players.

Consider
Players
PlayerId- PlayerName
1--Jim
2-John
3-Sam

Games/sport
1-Baseball
2-Skiing
3-Hockey
4-Soccer

Junction PlayerParticipatesIN

John participates in Hockey and Soccer
Sam participates in Soccer
Jim participates in Baseball, Skiing and Soccer

the junction table

PlayerID--------GameID
1.......................1===========Jim Baseball
1.......................2===========Jim Skiing
1.......................4===========Jim Soccer
2.......................3===========John Hockey
2.......................4===========John Soccer
3.......................4===========Sam Soccer

[orange]

Other fields as desired means
--If you have other fields that are dependent on this composite key, then this is where they would be recorded.