Complex SQL Query

SQL Apprentice Question
have three tables in my database, part of a tennis league results and

fixtures website I am putting together.


Results Table
id fixture_id home_team_rubbers away_team_rubbers
1 1 2 2
2 2 1 3
3 3 3 1
4 4 1 3
5 5 0 4
6 6 3 1
7 7 4 0
8 8 4 0
9 9 1 3
10 10 2 2
11 11 2 2
12 12 0 4
13 13 1 3
14 14 2 2
15 15 3 1
16 16 4 0
17 17 4 0
18 18 3 1
19 19 1 3
20 20 2 2
21 21 0 4
22 22 2 2
23 23 3 1
24 24 3 1


Fixtures Table
id home_team_id away_team_id
1 1 2
2 1 3
3 2 1
4 2 3
5 3 1
6 3 2
7 4 5
8 4 6
9 4 7
10 5 4
11 5 6
12 5 7
13 6 4
14 6 5
15 6 7
16 7 4
17 7 5
18 7 6
19 8 9
20 8 10
21 9 8
22 9 10
23 10 8
24 10 9


Team Table
id division_id club_id
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2
6 2 2
7 2 3
8 3 1
9 3 2
10 3 2


What I want to do it list the league table positions for all the teams
in all the divisions with the same club_id as myclub_id through an SQL
query.


The league table positions are determined by the total number of
rubbers acheived (total_rubbers_acheived) which is the sum of
home_team_rubbers and away_team_rubbers and then sorted so that the
highest is in position 1.


For example the league table for division_id = 1
team_id total_rubbers_acheived league_table_position
3 9 1
1 8 2
2 7 3


For example the league table for division_id = 2
team_id total_rubbers_acheived league_table_position
7 19 1
4 14 2
6 9 3
5 6 4


For example the league table for division_id = 3
team_id total_rubbers_acheived league_table_position
10 10 1
8 8 2
9 6 3


The resulting output from the query that I desire is the following.


Query Output - when myclub_id = 1
team_id division_id league_table_position
1 1 2
4 2 2
8 3 2


Query Output - when myclub_id = 2
team_id division_id league_table_position
2 1 3
5 2 4
6 2 3
9 3 3
10 3 1


Query Output - when myclub_id = 3
team_id division_id league_table_position
3 1 1
7 2 1


Any ideas how I can do this?


Celko Answers
>> Any ideas how I can do this? <<


Have you considered using a relational design instead mimicking a
magnetic tape file? There is no such thing as a vague, universal id
that you can use to mark all the things in creation. Auto-increment is
a way of saying that you have no idea what a key is - and it ain't
a physical locator generated by the hardware!

I see that teams have no names, that you have no referencing among the
tables, so they are totally unrelated. Instead of computing standing
and results, you seem to want to write them to physical storage, thus
missing the basic point that tables - unlike files - can be virtual
tables.


I also find it strange that you have results, but nobody plays a game
in your model. But then you have a model where everything is a
SMALLINT.


CREATE TABLE Teams
(team_id SMALLINT NOT NULL PRIMARY KEY,
team_name CHAR(15) NOT NULL,
team_div CHAR(15) NOT NULL,
etc.);


When you say "rubbers" it means you are playing Bridge to me (or
engaged in another of my favorite sports with proper protection).


I vaguely remember that you score 0 (love), 15, 30, and 40 points
which leads to four points to win a game, six games to win a set and
two (or three?) sets to win a match. I am not sure if you want to keep
each set or just match points. Either way, you need better CHECK()
constraints than I am showing here to enforce valid scoring.


CREATE TABLE Games
(home_team_id SMALLINT NOT NULL
REFERENCES Teams(team_id),
away_team_id SMALLINT NOT NULL
REFERENCES Teams(team_id),
CHECK (away_team_id <> home_team_id),
game_date DATE NOT NULL,
home_team_score SMALLINT NOT NULL
CHECK (home_team_score >= 0),
away_team_score SMALLINT NOT NULL
CHECK (away_team_score >= 0),
PRIMARY KEY (away_team_id, home_team_id, game_date)
);


Do you need to be sure that teams are in the same division? Etc. You
did not post a good spec and assumed that everyone plays competition
Tennis, so they know the terms.