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 <gto...@ffn.com> wrote: > 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.tompk...@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.braw...@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<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 >
