Interesting Query Problem!

Dear All,
I have three tables:
teams table
id name
--
1 Team A
2 Team B
3 Team C
4 Team D
fixtures table
id home team id away team id
---
1 1 2
2 1 3
3 1 4
4 2 1
5 2 3
6 2 4
7 3 1
8 3 2
9 3 4
10 4 1
11 4 2
12 4 3
results table
fixture id home team result away team result
-----
1 1 3
2 4 0
3 2 2
4 2 2
5 0 4
6 1 3
7 3 1
8 2 2
9 4 0
10 2 2
11 0 4
12 1 3
what I want to be able to to is generate a table (list) of teams as the
output from a sql query in an order dependant on two things:
1) number of points won (highest number of points at the top) and 2)
(which is where the interesting bit comes into it) where two or more
teams have the same number of points the position is to be determined
by examining the results between the two or more teams in question.
So the output I am looking for in this example:
output table
team name total team points notes
----
Team C 16 1.
Team B 12 2.
Team A 12 3.
Team D 8 4.
Note: 1. Team C have 16 points therefore are the clear leaders of the
table
Note: 2. AvB = 1-3 BvA = 2-2 therefore Team B [5pts] is above Team A
[3pts]
Note: 3. see note 2
Note: 4. Team D have 8 points therefore are last in the table
My question is how do I write an sql query that will interrogate these
three tables to give my the output as described above.
Many Thanks
Simonsimon.stockton@.baesystems.com wrote:
> [stuff]
Are BAE Systems branching out into Sunday 5-a-side football logistics
now? ;-)|||Bobbo,
This is an attempt at a rather simple example so that I can
explain/understand the theory behind how to solve the problem before
applying it to my particular situation.
Regards
Simon|||simon.stockton@.baesystems.com wrote:
> This is an attempt at a rather simple example so that I can
> explain/understand the theory behind how to solve the problem before
> applying it to my particular situation.
... and that was my attempt at humour, sorry.
This code below seems to work for me, although I'm sure someone else
will be able to suggest how it can be simplified as it looks
ridiculously over-complicated.
Also, the names have changed since your example, but I'm sure you'll
get the idea.
-- Outer query to sum home and away games and order by total points
select [name], sum(points) points
from
(
-- Top half of subquery to get points for home games
select t.[name], sum(s.home) points
from scores s
join fixes f on s.fixid = f.fixid
join teams t on f.homeid = t.teamid
group by t.[name]
union all
-- Second half of subquery to get points for away games
select t.[name], sum(s.away) points
from scores s
join fixes f on s.fixid = f.fixid
join teams t on f.awayid = t.teamid
group by t.[name]
) as sub
group by [name]
order by points desc|||Bobbo,
Thanks for that. I can see how that work.
However, it doesn't cover:
"2) (which is where the interesting bit comes into it) where two or
more
teams have the same number of points the position is to be determined
by examining the results between the two or more teams in question."
So once having used your query to generate a list, this list then needs
to be sorted in the event that two teams share the same number of
points, by looking at the individual points against the opponent team
who share the same number of points.
Given the data originally specified I don't think that your query will
generate the output table as shown earlier.
Getting there though!
Thanks
Simon|||The following code may do it for you:
set nocount on
create table teams
(
id int primary key
, name varchar (20) not null
)
go
insert teams values (1, 'Team A')
insert teams values (2, 'Team B')
insert teams values (3, 'Team C')
insert teams values (4, 'Team D')
go
create table fixtures
(
id int primary key
, home int not null
references teams (id)
, away int not null
references teams (id)
)
insert fixtures values (1, 1, 2)
insert fixtures values (2, 1, 3)
insert fixtures values (3, 1, 4)
insert fixtures values (4, 2, 1)
insert fixtures values (5, 2, 3)
insert fixtures values (6, 2, 4)
insert fixtures values (7, 3, 1)
insert fixtures values (8, 3, 2)
insert fixtures values (9, 3, 4)
insert fixtures values (10, 4, 1)
insert fixtures values (11, 4, 2)
insert fixtures values (12, 4, 3)
go
create table results
(
id int primary key
references fixtures (id)
, home int not null
, away int not null
)
go
insert results values (1, 1, 3)
insert results values (2, 4, 0)
insert results values (3, 2, 2)
insert results values (4, 2, 2)
insert results values (5, 0, 4)
insert results values (6, 1, 3)
insert results values (7, 3, 1)
insert results values (8, 2, 2)
insert results values (9, 4, 0)
insert results values (10, 2, 2)
insert results values (11, 0, 4)
insert results values (12, 1, 3)
go
with totals (teamid, total, wins)
as
(
select
case type
when 1 then f.home
when 2 then f.away
end
, sum (case type
when 1 then r.home
when 2 then r.away
end)
, sum (case
when type = 1 and sign (r.home - r.away) > 0 then 1
when type = 2 and sign (r.away - r.home) > 0 then 1
else 0
end)
from
fixtures f
join
results r on r.id = f.id
cross join
(
select 1 union all
select 2
) as types (type)
group by
case type
when 1 then f.home
when 2 then f.away
end
)
select
t2.name
, t1.total
from
totals t1
join
teams t2 on t2.id = t1.teamid
order by
total desc
, wins desc
go
drop table results
drop table fixtures
drop table teams
The use of the CTE is SQL 2005, but you can do it without it - just use a
derived table instead.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<simon.stockton@.baesystems.com> wrote in message
news:1155557745.509502.222530@.i42g2000cwa.googlegroups.com...
Dear All,
I have three tables:
teams table
id name
--
1 Team A
2 Team B
3 Team C
4 Team D
fixtures table
id home team id away team id
---
1 1 2
2 1 3
3 1 4
4 2 1
5 2 3
6 2 4
7 3 1
8 3 2
9 3 4
10 4 1
11 4 2
12 4 3
results table
fixture id home team result away team result
-----
1 1 3
2 4 0
3 2 2
4 2 2
5 0 4
6 1 3
7 3 1
8 2 2
9 4 0
10 2 2
11 0 4
12 1 3
what I want to be able to to is generate a table (list) of teams as the
output from a sql query in an order dependant on two things:
1) number of points won (highest number of points at the top) and 2)
(which is where the interesting bit comes into it) where two or more
teams have the same number of points the position is to be determined
by examining the results between the two or more teams in question.
So the output I am looking for in this example:
output table
team name total team points notes
----
Team C 16 1.
Team B 12 2.
Team A 12 3.
Team D 8 4.
Note: 1. Team C have 16 points therefore are the clear leaders of the
table
Note: 2. AvB = 1-3 BvA = 2-2 therefore Team B [5pts] is above Team A
[3pts]
Note: 3. see note 2
Note: 4. Team D have 8 points therefore are last in the table
My question is how do I write an sql query that will interrogate these
three tables to give my the output as described above.
Many Thanks
Simon