List:General Discussion« Previous MessageNext Message »
From:Neil Tompkins Date:August 20 2010 6:51pm
Subject:Re: Fixture List generation using MySQL
View as plain text  
Carl you don't wish go offer so sample code ?


On 19 Aug 2010, at 19:18, "Carl" <carl@stripped> wrote:

> 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" <gtowey@stripped>
> To: "Tompkins Neil" <neil.tompkins@stripped>; "[MySQL]"
> <mysql@stripped 
> >
> 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@stripped]
> 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@stripped> 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<b.id;
>>> union
>>> 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=1
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
Thread
Fixture List generation using MySQLTompkins Neil19 Aug
  • Re: Fixture List generation using MySQLPeter Brawley19 Aug
  • Re: Fixture List generation using MySQLCarl19 Aug
    • Re: Fixture List generation using MySQLNeil Tompkins20 Aug
Re: Fixture List generation using MySQLTompkins Neil19 Aug
  • Re: Fixture List generation using MySQLPeter Brawley19 Aug
    • Re: Fixture List generation using MySQLburhan.khalid19 Aug
  • RE: Fixture List generation using MySQLGavin Towey19 Aug
    • Re: Fixture List generation using MySQLTompkins Neil20 Aug