List:General Discussion« Previous MessageNext Message »
From:Gavin Towey Date:August 19 2010 5:50pm
Subject:RE: Fixture List generation using MySQL
View as plain text  
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
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