Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-08-14 : 08:32:17
|
| Brian writes "I wanna show the standings in a Tournament. I have made most of it. Now I just need to select goals concieved....I have the folowwing tables:Team idTeam NameMatch idMatch Date idTeamA idTeamBTeamMatch idTeamMatch idMatch idTeam Score PointSo I have this view allready:SELECT DISTINCT Team.idTurTeam, Team.Name, (SELECT COUNT(*) FROM TeamMatch WHERE Point = 2 AND TeamMatch.idTeam = Team.idTeam) AS Won, (SELECT COUNT(*) FROM TeamMatch WHERE Point = 1 AND TeamMatch.idTeam = Team.idTeam) AS Draw, (SELECT COUNT(*) FROM TeamMatch WHERE Point = 0 AND TeamMatch.idTeam = Team.idTeam) AS Lost, SUM(TurTeamMatch.Score) AS ScorePlus, SUM(TurTeamMatch.Score) AS ScoreMinus, SUM(TurTeamMatch.Point) AS PointFROM Team LEFT OUTER JOIN TeamMatch ON Team.idTeam = TeamMatch.idTeamGROUP BY Team.Name, Team.idTeamThe ScoreMinus is the problem. Now it shows ScorePlus again../Weje" |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-08-14 : 09:13:44
|
| What do you want ScoreMinus to be? That's not clear.Also you only seem to be storing one score. In most sports that have multiple teams, the number of scores is equal to the number of teams...."In the Sunnydale Slayer's season opener against the L.A. Devil's, the score was 47."Jay White{0} |
 |
|
|
r937
Posting Yak Master
112 Posts |
Posted - 2002-08-14 : 13:07:38
|
| both scores are in there, jay, just in separate rows... presumably... and as an aside, it might make for an interesting CHECK constraint to ensure that there's no game with only one score... but how would you insert the first row? :)brian, in order to calculate the "minus" score -- i.e. scored against a team by the team they played -- you have to sum the score from teamMatch rows where idTeam is *not* equal to the team you're looking at... but since this could easily be all other matches, you need to make sure you only sum those rows where idMatch is one of the matches played by the team... if you know what i meanif it were me, i'd have put Score and Point right into Match as ScoreA, ScoreB, PointsA, PointsB, but maybe it's too late for thatrudyhttp://rudy.ca/ |
|
|
|
|
|
|
|
|
Create View