--00151750e8949f73f4048e2dc6c1
Content-Type: text/plain; charset=ISO-8859-1
Hi,
I'm tasked with generating a list of fixtures from a table of teams, whereby
each team plays each other home and away. Does anyone have any experience
generating such information using MySQL ?
Thanks for any input.
Regards
Neil
--00151750e8949f73f4048e2dc6c1--
>I'm tasked with generating a list of fixtures from a table of teams, whereby
>each team plays each other home and away. Does anyone have any experience
>generating such information using MySQL ?
Basically ...
select a.id,b.id from tbl a join tbl b on a.id
select a.id,b.id from tbl a join tbl b on a.id>b.id;
PB
-----
On 8/19/2010 9:12 AM, Tompkins Neil wrote:
> Hi,
>
> I'm tasked with generating a list of fixtures from a table of teams, whereby
> each team plays each other home and away. Does anyone have any experience
> generating such information using MySQL ?
>
> Thanks for any input.
>
> Regards
> Neil
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
--0016364ed89c2aaa66048e303624
Content-Type: text/plain; charset=ISO-8859-1
I'm looking at a routine / script to create the fixtures like
team 1 vs team 2
team 3 vs team 4
team 5 vs team 6 etc
>
>
>
> On Thu, Aug 19, 2010 at 3:44 PM, Peter Brawley <
> peter.brawley@earthlink.net> wrote:
>
>>
>>
>> I'm tasked with generating a list of fixtures from a table of teams,
>>> whereby
>>> each team plays each other home and away. Does anyone have any
>>> experience
>>> generating such information using MySQL ?
>>>
>>
>> Basically ...
>>
>> select a.id,b.id from tbl a join tbl b on a.id
>> select a.id,b.id from tbl a join tbl b on a.id>b.id;
>>
>> PB
>>
>> -----
>>
>>
>> On 8/19/2010 9:12 AM, Tompkins Neil wrote:
>>
>>> Hi,
>>>
>>> I'm tasked with generating a list of fixtures from a table of teams,
>>> whereby
>>> each team plays each other home and away. Does anyone have any
>>> experience
>>> generating such information using MySQL ?
>>>
>>> Thanks for any input.
>>>
>>> Regards
>>> Neil
>>>
>>>
>
--0016364ed89c2aaa66048e303624--
>I'm looking at a routine / script to create the fixtures like
>team 1 vs team 2
>team 3 vs team 4
>team 5 vs team 6 etc
Build the script round the query.
PB
-----
On 8/19/2010 12:07 PM, Tompkins Neil wrote:
> I'm looking at a routine / script to create the fixtures like
>
> team 1 vs team 2
> team 3 vs team 4
> team 5 vs team 6 etc
>
>>
>>
>> On Thu, Aug 19, 2010 at 3:44 PM, Peter Brawley<
>> peter.brawley@earthlink.net> wrote:
>>
>>>
>>> I'm tasked with generating a list of fixtures from a table of teams,
>>>> whereby
>>>> each team plays each other home and away. Does anyone have any
>>>> experience
>>>> generating such information using MySQL ?
>>>>
>>> Basically ...
>>>
>>> select a.id,b.id from tbl a join tbl b on a.id
>>> select a.id,b.id from tbl a join tbl b on a.id>b.id;
>>>
>>> PB
>>>
>>> -----
>>>
>>>
>>> On 8/19/2010 9:12 AM, Tompkins Neil wrote:
>>>
>>>> Hi,
>>>>
>>>> I'm tasked with generating a list of fixtures from a table of teams,
>>>> whereby
>>>> each team plays each other home and away. Does anyone have any
>>>> experience
>>>> generating such information using MySQL ?
>>>>
>>>> Thanks for any input.
>>>>
>>>> Regards
>>>> Neil
>>>>
>>>>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
That's almost a cartesean product; except you just want to eliminate result=
s where a team would be paired up with itself.
> create table teams ( id serial );
Query OK, 0 rows affected (0.02 sec)
> insert into teams values (), (), (), ();
Query OK, 4 rows affected (0.05 sec)
Records: 4 Duplicates: 0 Warnings: 0
[ff] test> select * from teams;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
4 rows in set (0.00 sec)
> select * from locations;
+------+
| name |
+------+
| home |
| away |
+------+
2 rows in set (0.00 sec)
> select * from teams t1 JOIN teams t2;
+----+----+
| id | id |
+----+----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
| 4 | 2 |
| 1 | 3 |
| 2 | 3 |
| 3 | 3 |
| 4 | 3 |
| 1 | 4 |
| 2 | 4 |
| 3 | 4 |
| 4 | 4 |
+----+----+
16 rows in set (0.00 sec)
With no join condition, we every possible combination of t1 paired with t2;=
however, this leads to the undesireable result that we have combinations l=
ike team 4 vs team 4. So you just need to add a condition to prevent those=
rows from showing up:
> select * from teams t1 JOIN teams t2 ON t1.id!=3Dt2.id;
+----+----+
| id | id |
+----+----+
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 1 | 2 |
| 3 | 2 |
| 4 | 2 |
| 1 | 3 |
| 2 | 3 |
| 4 | 3 |
| 1 | 4 |
| 2 | 4 |
| 3 | 4 |
+----+----+
12 rows in set (0.10 sec)
Notice you get both combinations of 2 vs 1 and 1 vs 2, so you could just ca=
ll whichever team is in the first column as the "home team."
Regards,
Gavin Towey
-----Original Message-----
From: Tompkins Neil [mailto:neil.tompkins@googlemail.com]
Sent: Thursday, August 19, 2010 10:07 AM
To: [MySQL]
Subject: Re: Fixture List generation using MySQL
I'm looking at a routine / script to create the fixtures like
team 1 vs team 2
team 3 vs team 4
team 5 vs team 6 etc
>
>
>
> On Thu, Aug 19, 2010 at 3:44 PM, Peter Brawley <
> peter.brawley@earthlink.net> wrote:
>
>>
>>
>> I'm tasked with generating a list of fixtures from a table of teams,
>>> whereby
>>> each team plays each other home and away. Does anyone have any
>>> experience
>>> generating such information using MySQL ?
>>>
>>
>> Basically ...
>>
>> select a.id,b.id from tbl a join tbl b on a.id
>> select a.id,b.id from tbl a join tbl b on a.id>b.id;
>>
>> PB
>>
>> -----
>>
>>
>> On 8/19/2010 9:12 AM, Tompkins Neil wrote:
>>
>>> Hi,
>>>
>>> I'm tasked with generating a list of fixtures from a table of teams,
>>> whereby
>>> each team plays each other home and away. Does anyone have any
>>> experience
>>> generating such information using MySQL ?
>>>
>>> Thanks for any input.
>>>
>>> Regards
>>> Neil
>>>
>>>
>
This message contains confidential information and is intended only for the=
individual named. If you are not the named addressee, you are notified th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f you have received this e-mail by mistake and delete this e-mail from your=
system. E-mail transmission cannot be guaranteed to be secure or error-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate or incomplete, or contain viruses. The sender therefore does not accept=
liability for any loss or damage caused by viruses or errors or omissions =
in the contents of this message, which arise as a result of e-mail transmis=
sion. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089,=
USA, FriendFinder.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg
T25lIHBvc3NpYmxlIGFwcHJvYWNoOiBJbiB5b3VyIHNjcmlwdCwgZ2VuZXJh dGUgc29tZSBvcmRl
cmVkIGl0ZXJhYmxlIG9iamVjdCB3aXRoIGFuIGluZGV4IG9mIHlvdXIgdGVh bSBuYW1lcy4gU3Rl
cCB0aHJvdWdoIGl0IGluIGEgbG9vcCBhbmQgY2hlY2sgdGhlIGNvdW50ZXI7 IG1hdGNoICJvZGQi
IHRlYW1zIHdpdGggImV2ZW4iIHRlYW1zIGFuZCBnZW5lcmF0ZSB5b3VyIHF1 ZXJpZXMuDQoNCllv
dSBjb3VsZCBwcm9iYWJseSBkbyB0aGlzIG9uIHRoZSBzZXJ2ZXIgZW5kIGFz IHdlbGwgYXMgdGhl
IGxvZ2ljIGlzIHF1aXRlIHNpbXBsZS4NCg0KUmVnYXJkcywNCi0tDQpCdXJo YW4gS2hhbGlkDQpT
ZW50IGZyb20gbXkgQmxhY2tCZXJyea4gc21hcnRwaG9uZSBmcm9tIFdhdGFu aXlhIFRlbGVjb20N
Cg0KLS0tLS1PcmlnaW5hbCBNZXNzYWdlLS0tLS0NCkZyb206IFBldGVyIEJy YXdsZXkgPHBldGVy
LmJyYXdsZXlAZWFydGhsaW5rLm5ldD4NCkRhdGU6IFRodSwgMTkgQXVnIDIw MTAgMTI6NDg6MTgg
DQpUbzogPG15c3FsQGxpc3RzLm15c3FsLmNvbT4NClJlcGx5LVRvOiBwZXRl ci5icmF3bGV5QGVh
cnRobGluay5uZXQNClN1YmplY3Q6IFJlOiBGaXh0dXJlIExpc3QgZ2VuZXJh dGlvbiB1c2luZyBN
eVNRTA0KDQoNCg0KPkknbSBsb29raW5nIGF0IGEgcm91dGluZSAvIHNjcmlw dCB0byBjcmVhdGUg
dGhlIGZpeHR1cmVzIGxpa2UNCg0KPnRlYW0gMSB2cyB0ZWFtIDINCj50ZWFt IDMgdnMgdGVhbSA0
DQo+dGVhbSA1IHZzIHRlYW0gNiBldGMNCg0KQnVpbGQgdGhlIHNjcmlwdCBy b3VuZCB0aGUgcXVl
cnkuDQoNClBCDQoNCi0tLS0tDQoNCk9uIDgvMTkvMjAxMCAxMjowNyBQTSwg VG9tcGtpbnMgTmVp
bCB3cm90ZToNCj4gSSdtIGxvb2tpbmcgYXQgYSByb3V0aW5lIC8gc2NyaXB0 IHRvIGNyZWF0ZSB0
aGUgZml4dHVyZXMgbGlrZQ0KPg0KPiB0ZWFtIDEgdnMgdGVhbSAyDQo+IHRl YW0gMyB2cyB0ZWFt
IDQNCj4gdGVhbSA1IHZzIHRlYW0gNiBldGMNCj4NCj4+DQo+Pg0KPj4gT24g VGh1LCBBdWcgMTks
IDIwMTAgYXQgMzo0NCBQTSwgUGV0ZXIgQnJhd2xleTwNCj4+IHBldGVyLmJy YXdsZXlAZWFydGhs
aW5rLm5ldD4gIHdyb3RlOg0KPj4NCj4+Pg0KPj4+ICAgSSdtIHRhc2tlZCB3 aXRoIGdlbmVyYXRp
bmcgYSBsaXN0IG9mIGZpeHR1cmVzIGZyb20gYSB0YWJsZSBvZiB0ZWFtcywN Cj4+Pj4gd2hlcmVi
eQ0KPj4+PiBlYWNoIHRlYW0gcGxheXMgZWFjaCBvdGhlciBob21lIGFuZCBh d2F5LiAgRG9lcyBh
bnlvbmUgaGF2ZSBhbnkNCj4+Pj4gZXhwZXJpZW5jZQ0KPj4+PiBnZW5lcmF0 aW5nIHN1Y2ggaW5m
b3JtYXRpb24gdXNpbmcgTXlTUUwgPw0KPj4+Pg0KPj4+IEJhc2ljYWxseSAu Li4NCj4+Pg0KPj4+
IHNlbGVjdCBhLmlkLGIuaWQgZnJvbSB0YmwgYSBqb2luIHRibCBiIG9uIGEu aWQ8Yi5pZDsNCj4+
PiB1bmlvbg0KPj4+IHNlbGVjdCBhLmlkLGIuaWQgZnJvbSB0YmwgYSBqb2lu IHRibCBiIG9uIGEu
aWQ+Yi5pZDsNCj4+Pg0KPj4+IFBCDQo+Pj4NCj4+PiAtLS0tLQ0KPj4+DQo+ Pj4NCj4+PiBPbiA4
LzE5LzIwMTAgOToxMiBBTSwgVG9tcGtpbnMgTmVpbCB3cm90ZToNCj4+Pg0K Pj4+PiBIaSwNCj4+
Pj4NCj4+Pj4gSSdtIHRhc2tlZCB3aXRoIGdlbmVyYXRpbmcgYSBsaXN0IG9m IGZpeHR1cmVzIGZy
b20gYSB0YWJsZSBvZiB0ZWFtcywNCj4+Pj4gd2hlcmVieQ0KPj4+PiBlYWNo IHRlYW0gcGxheXMg
ZWFjaCBvdGhlciBob21lIGFuZCBhd2F5LiAgRG9lcyBhbnlvbmUgaGF2ZSBh bnkNCj4+Pj4gZXhw
ZXJpZW5jZQ0KPj4+PiBnZW5lcmF0aW5nIHN1Y2ggaW5mb3JtYXRpb24gdXNp bmcgTXlTUUwgPw0K
Pj4+Pg0KPj4+PiBUaGFua3MgZm9yIGFueSBpbnB1dC4NCj4+Pj4NCj4+Pj4g UmVnYXJkcw0KPj4+
PiBOZWlsDQo+Pj4+DQo+Pj4+DQoNCi0tIA0KTXlTUUwgR2VuZXJhbCBNYWls aW5nIExpc3QNCkZv
ciBsaXN0IGFyY2hpdmVzOiBodHRwOi8vbGlzdHMubXlzcWwuY29tL215c3Fs DQpUbyB1bnN1YnNj
cmliZTogICAgaHR0cDovL2xpc3RzLm15c3FsLmNvbS9teXNxbD91bnN1Yj1i dXJoYW4ua2hhbGlk
QGdtYWlsLmNvbQ0KDQo=
I have written this in both C and Java. It is very complex as, in real
life, you want to balance home and away, sequence the games so that the home
or away games are spread throughout the schedule, accomodate partial rounds
(10 team league where each team is to play 13 games), accomodate odd numbers
of teams (7,9,etc.) and create games for teams with short schedules and a
lot more. In addition, this is only the beginning as, once you have a
playing schedule, you need to assign the games to space which is much more
complicated than creating the schedule. Reporting the games is rather
trivial except for situations where games have been moved, teams have
dropped out or been forfeited out, etc.
Thanks,
Carl
Gavin - Sorry, didn't mean to send it to you privately... itchy trigger
finger.
----- Original Message -----
From: "Gavin Towey"
To: "Tompkins Neil"
Sent: Thursday, August 19, 2010 1:50 PM
Subject: RE: Fixture List generation using MySQL
That's almost a cartesean product; except you just want to eliminate results
where a team would be paired up with itself.
> create table teams ( id serial );
Query OK, 0 rows affected (0.02 sec)
> insert into teams values (), (), (), ();
Query OK, 4 rows affected (0.05 sec)
Records: 4 Duplicates: 0 Warnings: 0
[ff] test> select * from teams;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
4 rows in set (0.00 sec)
> select * from locations;
+------+
| name |
+------+
| home |
| away |
+------+
2 rows in set (0.00 sec)
> select * from teams t1 JOIN teams t2;
+----+----+
| id | id |
+----+----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
| 4 | 2 |
| 1 | 3 |
| 2 | 3 |
| 3 | 3 |
| 4 | 3 |
| 1 | 4 |
| 2 | 4 |
| 3 | 4 |
| 4 | 4 |
+----+----+
16 rows in set (0.00 sec)
With no join condition, we every possible combination of t1 paired with t2;
however, this leads to the undesireable result that we have combinations
like team 4 vs team 4. So you just need to add a condition to prevent those
rows from showing up:
> select * from teams t1 JOIN teams t2 ON t1.id!=t2.id;
+----+----+
| id | id |
+----+----+
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 1 | 2 |
| 3 | 2 |
| 4 | 2 |
| 1 | 3 |
| 2 | 3 |
| 4 | 3 |
| 1 | 4 |
| 2 | 4 |
| 3 | 4 |
+----+----+
12 rows in set (0.10 sec)
Notice you get both combinations of 2 vs 1 and 1 vs 2, so you could just
call whichever team is in the first column as the "home team."
Regards,
Gavin Towey
-----Original Message-----
From: Tompkins Neil [mailto:neil.tompkins@googlemail.com]
Sent: Thursday, August 19, 2010 10:07 AM
To: [MySQL]
Subject: Re: Fixture List generation using MySQL
I'm looking at a routine / script to create the fixtures like
team 1 vs team 2
team 3 vs team 4
team 5 vs team 6 etc
>
>
>
> On Thu, Aug 19, 2010 at 3:44 PM, Peter Brawley <
> peter.brawley@earthlink.net> wrote:
>
>>
>>
>> I'm tasked with generating a list of fixtures from a table of teams,
>>> whereby
>>> each team plays each other home and away. Does anyone have any
>>> experience
>>> generating such information using MySQL ?
>>>
>>
>> Basically ...
>>
>> select a.id,b.id from tbl a join tbl b on a.id
>> select a.id,b.id from tbl a join tbl b on a.id>b.id;
>>
>> PB
>>
>> -----
>>
>>
>> On 8/19/2010 9:12 AM, Tompkins Neil wrote:
>>
>>> Hi,
>>>
>>> I'm tasked with generating a list of fixtures from a table of teams,
>>> whereby
>>> each team plays each other home and away. Does anyone have any
>>> experience
>>> generating such information using MySQL ?
>>>
>>> Thanks for any input.
>>>
>>> Regards
>>> Neil
>>>
>>>
>
This message contains confidential information and is intended only for the
individual named. If you are not the named addressee, you are notified that
reviewing, disseminating, disclosing, copying or distributing this e-mail is
strictly prohibited. Please notify the sender immediately by e-mail if you
have received this e-mail by mistake and delete this e-mail from your
system. E-mail transmission cannot be guaranteed to be secure or error-free
as information could be intercepted, corrupted, lost, destroyed, arrive late
or incomplete, or contain viruses. The sender therefore does not accept
liability for any loss or damage caused by viruses or errors or omissions in
the contents of this message, which arise as a result of e-mail
transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA
94089, USA, FriendFinder.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=carl@etrak-plus.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
--0015175cdddaea9e29048e3d447d
Content-Type: text/plain; charset=ISO-8859-1
Gavin,
Thanks for the great reply, this is actually what I was looking for.
However, do you have any suggestions on how to order the fixtures / teams ?
Basically the query is returning the teams grouped together like :
'2', '1'
'3', '1'
'4', '1'
'1', '2'
'3', '2'
'4', '2'
'1', '3'
'2', '3'
'4', '3'
'1', '4'
'2', '4'
'3', '4'
But ideally I'm looking for the data to be returned like
2 v 1
3 v 4
1 v 3
4 v 2 etc
Any suggestions ?
Cheers
Neil
On Thu, Aug 19, 2010 at 6:50 PM, Gavin Towey
> That's almost a cartesean product; except you just want to eliminate
> results where a team would be paired up with itself.
>
> > create table teams ( id serial );
> Query OK, 0 rows affected (0.02 sec)
>
> > insert into teams values (), (), (), ();
> Query OK, 4 rows affected (0.05 sec)
> Records: 4 Duplicates: 0 Warnings: 0
>
> [ff] test> select * from teams;
> +----+
> | id |
> +----+
> | 1 |
> | 2 |
> | 3 |
> | 4 |
> +----+
> 4 rows in set (0.00 sec)
>
> > select * from locations;
> +------+
> | name |
> +------+
> | home |
> | away |
> +------+
> 2 rows in set (0.00 sec)
>
>
> > select * from teams t1 JOIN teams t2;
> +----+----+
> | id | id |
> +----+----+
> | 1 | 1 |
> | 2 | 1 |
> | 3 | 1 |
> | 4 | 1 |
> | 1 | 2 |
> | 2 | 2 |
> | 3 | 2 |
> | 4 | 2 |
> | 1 | 3 |
> | 2 | 3 |
> | 3 | 3 |
> | 4 | 3 |
> | 1 | 4 |
> | 2 | 4 |
> | 3 | 4 |
> | 4 | 4 |
> +----+----+
> 16 rows in set (0.00 sec)
>
>
> With no join condition, we every possible combination of t1 paired with t2;
> however, this leads to the undesireable result that we have combinations
> like team 4 vs team 4. So you just need to add a condition to prevent those
> rows from showing up:
>
> > select * from teams t1 JOIN teams t2 ON t1.id!=t2.id;
> +----+----+
> | id | id |
> +----+----+
> | 2 | 1 |
> | 3 | 1 |
> | 4 | 1 |
> | 1 | 2 |
> | 3 | 2 |
> | 4 | 2 |
> | 1 | 3 |
> | 2 | 3 |
> | 4 | 3 |
> | 1 | 4 |
> | 2 | 4 |
> | 3 | 4 |
> +----+----+
> 12 rows in set (0.10 sec)
>
>
> Notice you get both combinations of 2 vs 1 and 1 vs 2, so you could just
> call whichever team is in the first column as the "home team."
>
>
> Regards,
> Gavin Towey
>
> -----Original Message-----
> From: Tompkins Neil [mailto:neil.tompkins@googlemail.com]
> Sent: Thursday, August 19, 2010 10:07 AM
> To: [MySQL]
> Subject: Re: Fixture List generation using MySQL
>
> I'm looking at a routine / script to create the fixtures like
>
> team 1 vs team 2
> team 3 vs team 4
> team 5 vs team 6 etc
>
> >
> >
> >
> > On Thu, Aug 19, 2010 at 3:44 PM, Peter Brawley <
> > peter.brawley@earthlink.net> wrote:
> >
> >>
> >>
> >> I'm tasked with generating a list of fixtures from a table of teams,
> >>> whereby
> >>> each team plays each other home and away. Does anyone have any
> >>> experience
> >>> generating such information using MySQL ?
> >>>
> >>
> >> Basically ...
> >>
> >> select a.id,b.id from tbl a join tbl b on a.id
> >> select a.id,b.id from tbl a join tbl b on a.id>b.id;
> >>
> >> PB
> >>
> >> -----
> >>
> >>
> >> On 8/19/2010 9:12 AM, Tompkins Neil wrote:
> >>
> >>> Hi,
> >>>
> >>> I'm tasked with generating a list of fixtures from a table of teams,
> >>> whereby
> >>> each team plays each other home and away. Does anyone have any
> >>> experience
> >>> generating such information using MySQL ?
> >>>
> >>> Thanks for any input.
> >>>
> >>> Regards
> >>> Neil
> >>>
> >>>
> >
>
> This message contains confidential information and is intended only for the
> individual named. If you are not the named addressee, you are notified that
> reviewing, disseminating, disclosing, copying or distributing this e-mail is
> strictly prohibited. Please notify the sender immediately by e-mail if you
> have received this e-mail by mistake and delete this e-mail from your
> system. E-mail transmission cannot be guaranteed to be secure or error-free
> as information could be intercepted, corrupted, lost, destroyed, arrive late
> or incomplete, or contain viruses. The sender therefore does not accept
> liability for any loss or damage caused by viruses or errors or omissions in
> the contents of this message, which arise as a result of e-mail
> transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA
> 94089, USA, FriendFinder.com
>
--0015175cdddaea9e29048e3d447d--
Carl you don't wish go offer so sample code ?
On 19 Aug 2010, at 19:18, "Carl"
> I have written this in both C and Java. It is very complex as, in
> real
> life, you want to balance home and away, sequence the games so that
> the home
> or away games are spread throughout the schedule, accomodate partial
> rounds
> (10 team league where each team is to play 13 games), accomodate odd
> numbers
> of teams (7,9,etc.) and create games for teams with short schedules
> and a
> lot more. In addition, this is only the beginning as, once you have a
> playing schedule, you need to assign the games to space which is
> much more
> complicated than creating the schedule. Reporting the games is rather
> trivial except for situations where games have been moved, teams have
> dropped out or been forfeited out, etc.
>
> Thanks,
>
> Carl
>
> Gavin - Sorry, didn't mean to send it to you privately... itchy
> trigger finger.
>
> ----- Original Message ----- From: "Gavin Towey"
> To: "Tompkins Neil"
> Sent: Thursday, August 19, 2010 1:50 PM
> Subject: RE: Fixture List generation using MySQL
>
>
> That's almost a cartesean product; except you just want to eliminate
> results where a team would be paired up with itself.
>
>> create table teams ( id serial );
> Query OK, 0 rows affected (0.02 sec)
>
>> insert into teams values (), (), (), ();
> Query OK, 4 rows affected (0.05 sec)
> Records: 4 Duplicates: 0 Warnings: 0
>
> [ff] test> select * from teams;
> +----+
> | id |
> +----+
> | 1 |
> | 2 |
> | 3 |
> | 4 |
> +----+
> 4 rows in set (0.00 sec)
>
>> select * from locations;
> +------+
> | name |
> +------+
> | home |
> | away |
> +------+
> 2 rows in set (0.00 sec)
>
>
>> select * from teams t1 JOIN teams t2;
> +----+----+
> | id | id |
> +----+----+
> | 1 | 1 |
> | 2 | 1 |
> | 3 | 1 |
> | 4 | 1 |
> | 1 | 2 |
> | 2 | 2 |
> | 3 | 2 |
> | 4 | 2 |
> | 1 | 3 |
> | 2 | 3 |
> | 3 | 3 |
> | 4 | 3 |
> | 1 | 4 |
> | 2 | 4 |
> | 3 | 4 |
> | 4 | 4 |
> +----+----+
> 16 rows in set (0.00 sec)
>
>
> With no join condition, we every possible combination of t1 paired
> with t2; however, this leads to the undesireable result that we have
> combinations like team 4 vs team 4. So you just need to add a
> condition to prevent those rows from showing up:
>
>> select * from teams t1 JOIN teams t2 ON t1.id!=t2.id;
> +----+----+
> | id | id |
> +----+----+
> | 2 | 1 |
> | 3 | 1 |
> | 4 | 1 |
> | 1 | 2 |
> | 3 | 2 |
> | 4 | 2 |
> | 1 | 3 |
> | 2 | 3 |
> | 4 | 3 |
> | 1 | 4 |
> | 2 | 4 |
> | 3 | 4 |
> +----+----+
> 12 rows in set (0.10 sec)
>
>
> Notice you get both combinations of 2 vs 1 and 1 vs 2, so you could
> just call whichever team is in the first column as the "home team."
>
>
> Regards,
> Gavin Towey
>
> -----Original Message-----
> From: Tompkins Neil [mailto:neil.tompkins@googlemail.com]
> Sent: Thursday, August 19, 2010 10:07 AM
> To: [MySQL]
> Subject: Re: Fixture List generation using MySQL
>
> I'm looking at a routine / script to create the fixtures like
>
> team 1 vs team 2
> team 3 vs team 4
> team 5 vs team 6 etc
>
>>
>>
>>
>> On Thu, Aug 19, 2010 at 3:44 PM, Peter Brawley <
>> peter.brawley@earthlink.net> wrote:
>>
>>>
>>>
>>> I'm tasked with generating a list of fixtures from a table of teams,
>>>> whereby
>>>> each team plays each other home and away. Does anyone have any
>>>> experience
>>>> generating such information using MySQL ?
>>>>
>>>
>>> Basically ...
>>>
>>> select a.id,b.id from tbl a join tbl b on a.id
>>> select a.id,b.id from tbl a join tbl b on a.id>b.id;
>>>
>>> PB
>>>
>>> -----
>>>
>>>
>>> On 8/19/2010 9:12 AM, Tompkins Neil wrote:
>>>
>>>> Hi,
>>>>
>>>> I'm tasked with generating a list of fixtures from a table of
>>>> teams,
>>>> whereby
>>>> each team plays each other home and away. Does anyone have any
>>>> experience
>>>> generating such information using MySQL ?
>>>>
>>>> Thanks for any input.
>>>>
>>>> Regards
>>>> Neil
>>>>
>>>>
>>
>
> This message contains confidential information and is intended only
> for the individual named. If you are not the named addressee, you
> are notified that reviewing, disseminating, disclosing, copying or
> distributing this e-mail is strictly prohibited. Please notify the
> sender immediately by e-mail if you have received this e-mail by
> mistake and delete this e-mail from your system. E-mail transmission
> cannot be guaranteed to be secure or error-free as information could
> be intercepted, corrupted, lost, destroyed, arrive late or
> incomplete, or contain viruses. The sender therefore does not accept
> liability for any loss or damage caused by viruses or errors or
> omissions in the contents of this message, which arise as a result
> of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt
> court, Sunnyvale, CA 94089, USA, FriendFinder.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=carl@etrak-plus.com
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=neil.tompkins@googlemail. com
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org