From: Neil Tompkins Date: August 20 2010 6:51pm Subject: Re: Fixture List generation using MySQL List-Archive: http://lists.mysql.com/mysql/222625 Message-Id: MIME-Version: 1.0 (iPod Mail 7E18) Content-Type: text/plain; charset=us-ascii; format=flowed; delsp=yes Content-Transfer-Encoding: 7bit Carl you don't wish go offer so sample code ? On 19 Aug 2010, at 19:18, "Carl" 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" > To: "Tompkins Neil" ; "[MySQL]" > > 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>> 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=carl@stripped > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=neil.tompkins@stripped >