Sql Comparing two tables: website

I 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?
Cheers,
SimonCould you please post your DDL + INSERT statements of your data? That will
make it much easier to craft a solution.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<simon.stockton@.baesystems.com> wrote in message
news:1149416751.611482.138870@.u72g2000cwu.googlegroups.com...
I 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?
Cheers,
Simon|||CREATE TABLE `results` (
`id` SMALLINT NOT NULL AUTO_INCREMENT ,
`fixture_id` SMALLINT NOT NULL ,
`home_team_rubbers` SMALLINT NOT NULL ,
`away_team_rubbers` SMALLINT NOT NULL ,
UNIQUE (
`id`
)
) ENGINE = innodb;
INSERT INTO `results` ( `id` , `fixture_id` , `home_team_rubbers` ,
`away_team_rubbers` )
VALUES (
NULL , '1', '2', '2'
), (
NULL , '2', '1', '3'
), (
NULL , '2', '3', '1'
), (
NULL , '2', '1', '3'
), (
NULL , '2', '0', '4'
), (
NULL , '2', '3', '1'
), (
NULL , '2', '4', '0'
), (
NULL , '2', '4', '0'
), (
NULL , '2', '1', '3'
), (
NULL , '2', '2', '2'
), (
NULL , '2', '2', '2'
), (
NULL , '2', '0', '4'
), (
NULL , '2', '1', '3'
), (
NULL , '2', '2', '2'
), (
NULL , '2', '3', '1'
), (
NULL , '2', '4', '0'
), (
NULL , '2', '4', '0'
), (
NULL , '2', '3', '1'
), (
NULL , '2', '1', '3'
), (
NULL , '2', '2', '2'
), (
NULL , '2', '0', '4'
), (
NULL , '2', '2', '2'
), (
NULL , '2', '3', '1'
), (
NULL , '2', '3', '1'
);
CREATE TABLE `fixtures` (
`id` SMALLINT NOT NULL AUTO_INCREMENT ,
`home_team_id` SMALLINT NOT NULL ,
`away_team_id` SMALLINT NOT NULL ,
UNIQUE (
`id`
)
) ENGINE = innodb;
INSERT INTO `fixtures` ( `id` , `home_team_id` , `away_team_id` )
VALUES (
NULL , '1', '2'
), (
NULL , '1', '3'
), (
NULL , '2', '1'
), (
NULL , '2', '3'
), (
NULL , '3', '1'
), (
NULL , '3', '2'
), (
NULL , '4', '5'
), (
NULL , '4', '6'
), (
NULL , '4', '7'
), (
NULL , '5', '4'
), (
NULL , '5', '6'
), (
NULL , '5', '7'
), (
NULL , '6', '4'
), (
NULL , '6', '5'
), (
NULL , '6', '6'
), (
NULL , '7', '4'
), (
NULL , '7', '5'
), (
NULL , '7', '6'
), (
NULL , '8', '9'
), (
NULL , '8', '10'
), (
NULL , '9', '8'
), (
NULL , '9', '10'
), (
NULL , '10', '8'
), (
NULL , '10', '9'
);
CREATE TABLE `teams` (
`id` SMALLINT NOT NULL AUTO_INCREMENT ,
`division_id` SMALLINT NOT NULL ,
`club_id` SMALLINT NOT NULL ,
UNIQUE (
`id`
)
) ENGINE = innodb;
INSERT INTO `teams` ( `id` , `division_id` , `club_id` )
VALUES (
NULL , '1', '1'
), (
NULL , '1', '2'
), (
NULL , '1', '3'
), (
NULL , '2', '1'
), (
NULL , '2', '2'
), (
NULL , '2', '2'
), (
NULL , '2', '3'
), (
NULL , '3', '1'
), (
NULL , '3', '2'
), (
NULL , '3', '2'
);|||This code doesn't work in SQL Server.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
<simon.stockton@.baesystems.com> wrote in message
news:1149429772.119617.228190@.h76g2000cwa.googlegroups.com...
CREATE TABLE `results` (
`id` SMALLINT NOT NULL AUTO_INCREMENT ,
`fixture_id` SMALLINT NOT NULL ,
`home_team_rubbers` SMALLINT NOT NULL ,
`away_team_rubbers` SMALLINT NOT NULL ,
UNIQUE (
`id`
)
) ENGINE = innodb;
INSERT INTO `results` ( `id` , `fixture_id` , `home_team_rubbers` ,
`away_team_rubbers` )
VALUES (
NULL , '1', '2', '2'
), (
NULL , '2', '1', '3'
), (
NULL , '2', '3', '1'
), (
NULL , '2', '1', '3'
), (
NULL , '2', '0', '4'
), (
NULL , '2', '3', '1'
), (
NULL , '2', '4', '0'
), (
NULL , '2', '4', '0'
), (
NULL , '2', '1', '3'
), (
NULL , '2', '2', '2'
), (
NULL , '2', '2', '2'
), (
NULL , '2', '0', '4'
), (
NULL , '2', '1', '3'
), (
NULL , '2', '2', '2'
), (
NULL , '2', '3', '1'
), (
NULL , '2', '4', '0'
), (
NULL , '2', '4', '0'
), (
NULL , '2', '3', '1'
), (
NULL , '2', '1', '3'
), (
NULL , '2', '2', '2'
), (
NULL , '2', '0', '4'
), (
NULL , '2', '2', '2'
), (
NULL , '2', '3', '1'
), (
NULL , '2', '3', '1'
);
CREATE TABLE `fixtures` (
`id` SMALLINT NOT NULL AUTO_INCREMENT ,
`home_team_id` SMALLINT NOT NULL ,
`away_team_id` SMALLINT NOT NULL ,
UNIQUE (
`id`
)
) ENGINE = innodb;
INSERT INTO `fixtures` ( `id` , `home_team_id` , `away_team_id` )
VALUES (
NULL , '1', '2'
), (
NULL , '1', '3'
), (
NULL , '2', '1'
), (
NULL , '2', '3'
), (
NULL , '3', '1'
), (
NULL , '3', '2'
), (
NULL , '4', '5'
), (
NULL , '4', '6'
), (
NULL , '4', '7'
), (
NULL , '5', '4'
), (
NULL , '5', '6'
), (
NULL , '5', '7'
), (
NULL , '6', '4'
), (
NULL , '6', '5'
), (
NULL , '6', '6'
), (
NULL , '7', '4'
), (
NULL , '7', '5'
), (
NULL , '7', '6'
), (
NULL , '8', '9'
), (
NULL , '8', '10'
), (
NULL , '9', '8'
), (
NULL , '9', '10'
), (
NULL , '10', '8'
), (
NULL , '10', '9'
);
CREATE TABLE `teams` (
`id` SMALLINT NOT NULL AUTO_INCREMENT ,
`division_id` SMALLINT NOT NULL ,
`club_id` SMALLINT NOT NULL ,
UNIQUE (
`id`
)
) ENGINE = innodb;
INSERT INTO `teams` ( `id` , `division_id` , `club_id` )
VALUES (
NULL , '1', '1'
), (
NULL , '1', '2'
), (
NULL , '1', '3'
), (
NULL , '2', '1'
), (
NULL , '2', '2'
), (
NULL , '2', '2'
), (
NULL , '2', '3'
), (
NULL , '3', '1'
), (
NULL , '3', '2'
), (
NULL , '3', '2'
);|||I am actually using MySQL, sorry I not sure what the differences are
and hence why it wouldn't worl, perhaps the ENGINE value is not correct!|||Well, this is a SQL Server newsgroup. As such, we make the assumption that
the problems you post here are for SQL Server.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
<simon.stockton@.baesystems.com> wrote in message
news:1149435355.608589.113130@.c74g2000cwc.googlegroups.com...
I am actually using MySQL, sorry I not sure what the differences are
and hence why it wouldn't worl, perhaps the ENGINE value is not correct!|||>> 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.

Can any one tell me about the comparision of SQL Server 2000 & SQL Server
2005. Like any website where I can find the enhacement like
1)
SQL Server 2005 :
We can pass the dynamic value of top in the
procedure.
Select Top @.Top from Table
SQL Server 2000 :
We can't you this and for implementing this we
have to make dynamic query.
2) Datatype :
Date in SQL Server 2005
Time in SQL Server 2005
XML in SQL Server 2005
Varchar(8000) in SQL Server 2000
Varchar(MAX) in SQL Server 2005
Can any one let me know like this sort of comparion I can find in which
website?
Thanks
If you install the SQL Server 2005 beta builds Books OnLine, there's a
section that covers "What's New in SQL Server 2005" for various components
including relational engine, TSQL, Replication, Analysis Services,
Management, Security, etc...
You can download the latest beta/Community Tech Preview from
http://www.microsoft.com/sql/2005/productinfo/ctp.mspx
joe.
"Roy" <roy@.hotmail.com> wrote in message
news:%23legtHAjFHA.576@.TK2MSFTNGP15.phx.gbl...
> Can any one tell me about the comparision of SQL Server 2000 & SQL Server
> 2005. Like any website where I can find the enhacement like
> 1)
> SQL Server 2005 :
> We can pass the dynamic value of top in the
> procedure.
> Select Top @.Top from Table
> SQL Server 2000 :
> We can't you this and for implementing this we
> have to make dynamic query.
>
> 2) Datatype :
> Date in SQL Server 2005
> Time in SQL Server 2005
> XML in SQL Server 2005
> Varchar(8000) in SQL Server 2000
> Varchar(MAX) in SQL Server 2005
>
> Can any one let me know like this sort of comparion I can find in which
> website?
> Thanks
>
>
>

Thursday, March 8, 2012

Complex SQL Query!

Friday, February 17, 2012

Compatiblilty

Compatiblilty

Compatiblilty

Compatiblilty

Compatiblilty

Friday, February 10, 2012

Comparision of SQL 2000 & 2005

Sql Comparing two tables

Blog Archive

About Me