League table problems - correct table design?

Hi all,
I have a problem designing the tables for a leasgue table calculating
database. The current structure is this (irrelavent bits omitted)

tabTeam(TeamID, TeamName)
------

tabPlayer(PlayerID, TeamID)
--------

tabGame(GameID, Team1ID, Team2ID, Team1Score, Team2Score)
------

However, I need to link TeamID to both Team1ID and Team2ID. There is
no doubt a simple solution to this, but I can't find it. I even tried
help!

If anyone has a league database that I could look at, I'd be very
grateful!

Many thanks
Richard Williamson

re: League table problems - correct table design?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


The tabGame should be like this:

CREATE TABLE tabGame (
GameID NOT NULL ,
TeamID NOT NULL ,
Score NOT NULL,
PRIMARY KEY (GameID, TeamID)
)

Now you can link to the TeamID.

Example of data in tabGame:

GameID TeamID Score
1 1 0
1 2 2
2 1 2
2 5 0

To get the games that TeamID 1 won:

SELECT GameID, Score
FROM tabGame As A
WHERE TeamID = 1
AND Score = (SELECT MAX(Score) FROM tabGame WHERE GameID = A.GameID)

Results:
GameID Score
2 2

To get the results of GameID 2:

SELECT TeamID, Score
FROM tabGame
WHERE GameID = 2

Results:
TeamID Score
1 2
5 0

- --
MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP6FspYechKqOuFEgEQLwDQCg/7cG5k19cFTPdceQemsttFXxHsEAnR4+
+8D4OhDc1xFKasj9ar01unSP
=sHTU
-----END PGP SIGNATURE-----

Richard Williamson wrote:
[color=blue]
> Hi all,
> I have a problem designing the tables for a leasgue table calculating
> database. The current structure is this (irrelavent bits omitted)
>
> tabTeam(TeamID, TeamName)
> ------
>
> tabPlayer(PlayerID, TeamID)
> --------
>
> tabGame(GameID, Team1ID, Team2ID, Team1Score, Team2Score)
> ------
>
> However, I need to link TeamID to both Team1ID and Team2ID. There is
> no doubt a simple solution to this, but I can't find it. I even tried
> help!
>
> If anyone has a league database that I could look at, I'd be very
> grateful!
>
> Many thanks
> Richard Williamson[/color]

re: League table problems - correct table design?

Hopefully this is what you're looking for:

Select tabGame.GameID, tabTeam1.TeamName, tabTeam2.TeamName,
tabGame.Team1Score, tabGame.Team2Score
From (tabGame Left Join tabTeam as TabTeam1 On tabGame.TeamID1 =
tabTeam1.TeamID) Left Join tabTeam as tabTeam2 On tabGame.TeamID2 =
tabTeam2.TeamID

I would also suggest normalizing the tables a bit more as suggested by
MGFoster - it would make for easier queries, etc.

richard.williamson1@ntlworld.com (Richard Williamson) wrote in message news:<62fa2974.0310300850.2001c9e3@posting.google. com>...[color=blue]
> Hi all,
> I have a problem designing the tables for a leasgue table calculating
> database. The current structure is this (irrelavent bits omitted)
>
> tabTeam(TeamID, TeamName)
> ------
>
> tabPlayer(PlayerID, TeamID)
> --------
>
> tabGame(GameID, Team1ID, Team2ID, Team1Score, Team2Score)
> ------
>
> However, I need to link TeamID to both Team1ID and Team2ID. There is
> no doubt a simple solution to this, but I can't find it. I even tried
> help!
>
> If anyone has a league database that I could look at, I'd be very
> grateful!
>
> Many thanks
> Richard Williamson[/color]