List:General Discussion« Previous MessageNext Message »
From:Tompkins Neil Date:August 20 2010 8:41am
Subject:Re: Fixture List generation using MySQL
View as plain text  
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 <gtowey@stripped> 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.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