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

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

Interesting ID issues...

Hi all,
I have a system in place currently that both a local and a live database, currently the sales database is kept in line with a home grown "syncronise" script that controls keeping the two tables (live and local) in sync with the use of Local and Remote ID's

For example;
Table "Sales"
ID - Int
RemoteID - Int
Various Data fields

The 'Live' System writes new records using the RemoteID field, whilst the local inserts new records using the ID field. The Sync script copies the data up/down and assigns the corresponding RemoteID/ID's to the records as they are copied accordingly.

Its been decided that this process must change, in favor of Two Way Replication. I have been tasked with finding the solution to the safe handling of ID's as theres a danger that two people can be writing a record (one live, one local) and possibly create the same ID's - i know that replication has its own ID's and could probably manage this well on its own, but these 'Internal' ID's have to be unique due to business rules.

So i have been considering two possibilities to address this problem,
1. Staggering the ID's - for instance Live ID's begin at 1,000,001 and local ID's begin at 1.
2. Having a controlling ID table which provides the next ID

Both options have their advantages and disadvantages, with option 1 we will run into a problem later when the local ID's reach the 1 million mark (a few years away, i admit) and option 2 will need careful locking/transactional code to ensure the same ID isnt given twice.

Does anyone have experience with this kind of issue? or could suggest an alternative approach to this problem?

Thanks in advance!

Replication ( of any type) by itself does not genrerate any Id's for you. Replication is just a process of copying the data from A to B and/or B to A. You can however, set up 2 range's of Id's for the 2 columns but like you mentioned you could run into issues in future and it could be sooner than later depending on how your data changes. And option 2 does create locking issues.

Now my question is since the 2 columns are different what is the need to keep them in the same table? Are local Id's from your local testing or do they hold any significance? I am trying to understand your design. Looks like you have 2 tables with same data (in 2 db's over 2 servers)?

There is a Peer-Peer Replication option available in SQL 2005 where you can insert data in either of the peer's and replication will take care of sync'ing them.

|||

I work for a Dotcom and we have two databases, one on our 'hosted' live servers and one in the local office.

Historically we have used two seperate ID fields, one live (RemoteID) and one local (ID), to allow staff to create sales records locally and customers to create sales records on the live systems. We have a script that then pulls down/copies up the new records and manages the ID's. Due to our sales increasing over the last 12 months this script can no longer handle the work in a timely fashion so another solution is required.

The ID numbers need to be the same, locally and live as this is part of the reference number given to the customer, its been communicated to me that the company wants to keep only a single ID record for sales now and not have to manage multiple ID fields, the company also wants to use replication to manage the data movement. So i need to find a solution for this.

The database can get quite busy under peak load, so i need a solution thats fast, efficient and capable of handling a lot of new records whilst still allowing staff to create local sales and customers to create live sales simultaneously without getting the ID's mixed up.

Using a control table for ID's was a thought, but my concern is speed and locking under heavy load

Interdev, SQL Server 2000 vs. SQL Server 7; open/design problems

I run Visual Interdev 6... I have 2 web servers and 2 database
servers... (1 of each located in my home - A; and 1 of each co-located
at an ISP - B with our own firewall at each location).
Location A:
Firewall, port 1433 opened (for specific IP#'s)
Web Server: IIS5
SQL Server: 7
Location B:
Firewall, port 1433 opened (for specific IP#'s)
Web Server: IIS6
SQL Server: 2000
For both locations, I can edit files and open databases. For location
A, I can also DESIGN databases (create new ones, edit existing ones)
through InterDev. I can NOT do this at Location B and its driving me
nuts. While I can open databases and view the contents, I can not
create new databases through Interdev nor add fields to existing ones.
I've made sure that BUILTIN/Administrators has the same access (and I'm
an administrator on all 4 servers) on both SQL servers.
Any ideas what I am missing here? Its driving me nuts having to
terminal service into Location B's database server everytime I want to
create a new database or even a new field!
I should mention that while I'm away from home, I can still design
databases from my home servers so it isn't being "inside" a firewall
vs. "outside". I have the exact same problem no matter WHERE I am
connected from (I tend to travel alot).

Interdev, SQL Server 2000 vs. SQL Server 7; open/design problems

I run Visual Interdev 6... I have 2 web servers and 2 database
servers... (1 of each located in my home - A; and 1 of each co-located
at an ISP - B with our own firewall at each location).
Location A:
Firewall, port 1433 opened (for specific IP#'s)
Web Server: IIS5
SQL Server: 7
Location B:
Firewall, port 1433 opened (for specific IP#'s)
Web Server: IIS6
SQL Server: 2000
For both locations, I can edit files and open databases. For location
A, I can also DESIGN databases (create new ones, edit existing ones)
through InterDev. I can NOT do this at Location B and its driving me
nuts. While I can open databases and view the contents, I can not
create new databases through Interdev nor add fields to existing ones.
I've made sure that BUILTIN/Administrators has the same access (and I'm
an administrator on all 4 servers) on both SQL servers.
Any ideas what I am missing here? Its driving me nuts having to
terminal service into Location B's database server everytime I want to
create a new database or even a new field!I should mention that while I'm away from home, I can still design
databases from my home servers so it isn't being "inside" a firewall
vs. "outside". I have the exact same problem no matter WHERE I am
connected from (I tend to travel alot).