469,945 Members | 2,262 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,945 developers. It's quick & easy.

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
Nov 12 '05 #1
2 2627
-----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:
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


Nov 12 '05 #2
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.

ri*****************@ntlworld.com (Richard Williamson) wrote in message news:<62**************************@posting.google. com>...
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

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Ram | last post: by
13 posts views Thread by Giggle Girl | last post: by
22 posts views Thread by klenwell | last post: by
6 posts views Thread by mtgriffiths86 | last post: by
2 posts views Thread by minus | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.