By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,234 Members | 1,823 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,234 IT Pros & Developers. It's quick & easy.

League table problems - correct table design?

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
-----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

P: n/a
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.