Question on a query to find all possible combinations of a set

**Thomas Bartkus** · Jul 23 '05, 06:59 AM

Re: Question on a query to find all possible combinations of a set

"Jonathan" <jonathan@heela l.nl> wrote in message
news:d413c5$jmn $1@reader08.wxs .nl...[color=blue]
>
> For a match schedule I would like to find all possible combinations of
> teams playing home and away (without teams playing to themselves of[/color]
course).[color=blue]
>
> I now the simple version works something like this:
>
> For a (very) simple table containing three rows like this:
>
> row 1: A
> row 2: B
> row 3: C
>
> SELECT DISTINCT
> t1.*, t2.* FROM t1, t1 as t2
> WHERE
> t1.id!=t2.id
> ORDER BY
> t1.id, t2.id;
>
> would result in the following solution:
>
> col1 col2
> row1 A B
> row2 A C
> row3 B A
> row4 B C
> row5 C A
> row6 C B
>[/color]

A few points here.

First, your DISTINCT clause is entirely unneccessary. You are using a cross
join to get every possible combination. This is an elegant and effective
method to get every possible combination. You will note that you get the
same results with and without the DISTINCT clause. This is because your
cross join already produces all unique (distinct!) combinations by design.

And you very neatly eliminate the situation of a team playing itself with
your WHERE exclusion. I might rewrite it something like this.

SELECT TeamsA.Team, TeamsB.Team
FROM tbl_Teams AS TeamsA, tbl_Teams As TeamsB
WHERE TeamsA.Team <> TeamsB.Team;

The DISTINCT clause is unneccessary and irreleveant here.

Okay - you may notice that you produce (TeamX vs. TeamY) as well as (TeamY
vs. TeamX). This is exactly what we expect from a cross join. A simple sql
solution to eliminate these redundant pairs does not come to (my limited!
:-) mind. I would resort to filtering these redundant pairs outside of
MySQL. Perhaps someone else can suggest a more elegant sql query solution.
[color=blue]
> To find all the teams in a certain group I need to use the following[/color]
query:[color=blue]
>
> SELECT DISTINCT
> team
> FROM
> program
> WHERE
> program.group = 'D2B'
> AND (
> id_season=IF(DA TE_FORMAT(CURRE NT_DATE(), '%m%d') >= '1101'
> OR
> DATE_FORMAT(CUR RENT_DATE(), '%m%d') < '0401' , 0, 4)
> OR
> id_season= IF(DATE_FORMAT( CURRENT_DATE(), '%m%d') >= '1101'
> OR
> DATE_FORMAT(CUR RENT_DATE(), '%m%d') < '0401' , 0,
> IF(DATE_FORMAT( CURRENT_DATE(), '%m%d') > '0715', 1, 3))
> );
>
> As a relatively newbie on (my)SQL I am having trouble converting the
> simple query stated above to a more extended one, that includes the last
> query to find the teams in the group, for which I would like to find all
> possible combinations of matches to be played.[/color]

Your WHERE clause here is unnecessarily complex and most certainly
susceptible to simplification. Since I don't know how to divine your
intentions here, I can't make a suggestion other than to ask you to tell us
what you are trying to achieve.
[color=blue]
> I'm using MySQL 4.0.22 so I believe subqueries are not an option as I
> can not upgrade to a newer version for the moment.[/color]

My standard workaround for dancing around the lack of subqueries is to use
temporary tables. You can always place "CREATE TEMPORARY TABLE {whatever}"
ahead of a SELECT query and then perform another query against your
temporary table. This is a *great* way to break a single complex query into
2 or more simpler queries. I actually prefer it to using subqueries which
only produces complicated, hard to debug code anyway. And - you have
TEMPORARY TABLES available to you with MySQL 4.0.22 .

Hope this is of some help
Thomas Bartkus

**Jonathan** · Jul 23 '05, 06:59 AM

Re: Question on a query to find all possible combinations of a set

Thomas Bartkus wrote:[color=blue]
> "Jonathan" <jonathan@heela l.nl> wrote in message
> news:d413c5$jmn $1@reader08.wxs .nl...
>[color=green]
>>For a match schedule I would like to find all possible combinations of
>>teams playing home and away (without teams playing to themselves of[/color]
>
> course).[/color]
[color=blue]
> I might rewrite it something like this.
>
> SELECT TeamsA.Team, TeamsB.Team
> FROM tbl_Teams AS TeamsA, tbl_Teams As TeamsB
> WHERE TeamsA.Team <> TeamsB.Team;
>
> The DISTINCT clause is unneccessary and irreleveant here.
>
> Okay - you may notice that you produce (TeamX vs. TeamY) as well as (TeamY
> vs. TeamX). This is exactly what we expect from a cross join. A simple sql
> solution to eliminate these redundant pairs does not come to (my limited!
> :-) mind. I would resort to filtering these redundant pairs outside of
> MySQL. Perhaps someone else can suggest a more elegant sql query solution.
>[/color]
I'm not bothered by the redundant pairs as all teams play both home and
away... so no need for filtering there.[color=blue]
>[color=green]
>>To find all the teams in a certain group I need to use the following[/color]
>
> query:
>[color=green]
>>SELECT DISTINCT
>> team
>>FROM
>> program
>>WHERE
>> program.group = 'D2B'
>>AND (
>> id_season=IF(DA TE_FORMAT(CURRE NT_DATE(), '%m%d') >= '1101'
>>OR
>> DATE_FORMAT(CUR RENT_DATE(), '%m%d') < '0401' , 0, 4)
>>OR
>> id_season= IF(DATE_FORMAT( CURRENT_DATE(), '%m%d') >= '1101'
>>OR
>> DATE_FORMAT(CUR RENT_DATE(), '%m%d') < '0401' , 0,
>> IF(DATE_FORMAT( CURRENT_DATE(), '%m%d') > '0715', 1, 3))
>>);
>>
>>As a relatively newbie on (my)SQL I am having trouble converting the
>>simple query stated above to a more extended one, that includes the last
>>query to find the teams in the group, for which I would like to find all
>>possible combinations of matches to be played.[/color]
>
>
> Your WHERE clause here is unnecessarily complex and most certainly
> susceptible to simplification. Since I don't know how to divine your
> intentions here, I can't make a suggestion other than to ask you to tell us
> what you are trying to achieve.
>[/color]

I know my where clause is a bit complex but unfortunately (IMHO)
neccesary to find the teams based on the league/competition they are
playing at this year.
All teams play an indoor ( id_season=2 ) and and outdoor competition.
The outdoor season is split into two seasons because of the bad weather
in our country ( The weather can be harsh, during this time the indoor
competition is played ).

Certain teams play against the same teams before and after the indoor
competition ( id_season=4), other teams play two separted outdoor
competitions with different teams in a group. The first season (
id_season=1 ) starts before the indoor competition and the second one
starts after the indoor competition ends ( id_season = 3 ). The last
group of leages plays against the same teams before and after the indoor
competition ( therefore there are more teams in those leagues ) and for
them id_season = 4.

I can give you a simplofoed query by evaluating the query for today
(because I don't have a table which group consists of which teams when)
I filter the database based on the id_season code as a team is either
playing the second half of their league (4) or playing the second league
for this group this year (3):

SELECT DISTINCT
team
FROM
program
WHERE
program.group = 'D2B'
AND
(
id_season= 4
OR
id_season= 3
)

Can you now (with the evaluated more simple query) help me join the two
queries together to find all the possible matches?

I was thinking somewhere along the line of this:

SELECT DISTINCT
teamsA.home as hometeam, teamsB.home as awayteam
FROM
program as teamsA, program As teamsB
WHERE
teamsA.group='D 2B' AND teamsB.group='D 2B'
AND
teamsA.home != teamsB.away
AND
(
(teamsA.id_seas on=3 or teamsA.id_seaso n=4)
AND
(teamsB.id_seas on=3 or teamsB.id_seaso n=4)
)
ORDER BY
hometeam, awayteam;

I'm hoping and guessing that there is some other and better way to do
this as I need to specify the criteria for both tables and have to use
the DISTINCT keyword as well to reduce the redundant records (7 fold
when the group consists of 8 teams).
[color=blue][color=green]
>>I'm using MySQL 4.0.22 so I believe subqueries are not an option as I
>>can not upgrade to a newer version for the moment.[/color]
>
>
> My standard workaround for dancing around the lack of subqueries is to use
> temporary tables. You can always place "CREATE TEMPORARY TABLE {whatever}"
> ahead of a SELECT query and then perform another query against your
> temporary table. This is a *great* way to break a single complex query into
> 2 or more simpler queries. I actually prefer it to using subqueries which
> only produces complicated, hard to debug code anyway. And - you have
> TEMPORARY TABLES available to you with MySQL 4.0.22 .[/color]

Or is creating temporary tables the only solution? And if so: If I use
temporary tables are they deleted after closing the connection or do I
have to take care of that in my code?
[color=blue]
> Hope this is of some help
> Thomas Bartkus[/color]

It certainly is... learned a lot!

Jonathan

--
A: Because it messes up the order in which people normally read text.
Q: Why is it such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?

**Thomas Bartkus** · Jul 23 '05, 06:59 AM

Re: Question on a query to find all possible combinations of a set

"Jonathan" <jonathan@heela l.nl> wrote in message
news:d43ens$mmq $1@reader13.wxs .nl...
[color=blue][color=green]
> > "Jonathan" <jonathan@heela l.nl> wrote in message[/color][/color]
[color=blue]
>
> I know my where clause is a bit complex but unfortunately (IMHO)
> neccesary to find the teams based on the league/competition they are
> playing at this year.
> All teams play an indoor ( id_season=2 ) and and outdoor competition.
> The outdoor season is split into two seasons because of the bad weather
> in our country ( The weather can be harsh, during this time the indoor
> competition is played ).
>
> Certain teams play against the same teams before and after the indoor
> competition ( id_season=4), other teams play two separted outdoor
> competitions with different teams in a group. The first season (
> id_season=1 ) starts before the indoor competition and the second one
> starts after the indoor competition ends ( id_season = 3 ). The last
> group of leages plays against the same teams before and after the indoor
> competition ( therefore there are more teams in those leagues ) and for
> them id_season = 4.
>
> I can give you a simplofoed query by evaluating the query for today
> (because I don't have a table which group consists of which teams when)
> I filter the database based on the id_season code as a team is either
> playing the second half of their league (4) or playing the second league
> for this group this year (3):
>
> SELECT DISTINCT
> team
> FROM
> program
> WHERE
> program.group = 'D2B'
> AND
> (
> id_season= 4
> OR
> id_season= 3
> )[/color]

Slightly cleaner/leaner:
AND (id_season IN (3, 4))
[color=blue]
> Can you now (with the evaluated more simple query) help me join the two
> queries together to find all the possible matches?
>
> I was thinking somewhere along the line of this:
>
> SELECT DISTINCT
> teamsA.home as hometeam, teamsB.home as awayteam
> FROM
> program as teamsA, program As teamsB
> WHERE
> teamsA.group='D 2B' AND teamsB.group='D 2B'
> AND
> teamsA.home != teamsB.away
> AND
> (
> (teamsA.id_seas on=3 or teamsA.id_seaso n=4)
> AND
> (teamsB.id_seas on=3 or teamsB.id_seaso n=4)
> )
> ORDER BY
> hometeam, awayteam;
>
> I'm hoping and guessing that there is some other and better way to do
> this as I need to specify the criteria for both tables and have to use
> the DISTINCT keyword as well to reduce the redundant records (7 fold
> when the group consists of 8 teams).[/color]

Okay. I see now that you are drawing your team names from a table where any
single team may appear in more than one record. It seems you should have a
simple list of teams somewhere. No matter. You can get it on the fly.

CREATE TEMPORARY TABLE TeamList
SELECT DISTINCT home As TeamName
FROM program
WHERE (home.group = 'D2B')
AND (id_season IN (3,4));

Now you have a perfectly good table called "TeamList" with the names of all
teams in group 'D2B' with id_season = 3 or id_season = 4. The bulk of your
filtering is done.

SELECT HomeTeams.TeamN ame as HomeTeam,
AwayTeams.TeamN ame as AwayTeam
FROM TeamList As HomeTeams, TeamList As AwayTeams
ORDER BY HomeTeam, AwayTeam;

DROP TABLE TeamList;

That last DROP TABLE is not strictly necessary. Temporary tables are just
that - temporary. As soon as you close your connection, they disappear.
However, it is always good practice to deliberately DROP what you CREATE if
you don't want to keep something hanging around, even if the system is
friendly enough to do some of your housecleaning for you.
[color=blue]
> Or is creating temporary tables the only solution?[/color]
That's doubtful. But -
I think it is the *cleanest* solution to compensate for the lack of nested
queries.
And
It is a great way to simplify some of the excruciatingly complicated queries
we all seem to blunder about with because you can use temporary tables to
bust them up into sequences of 2 or more simpler queries.
[color=blue]
> And if so: If I use
> temporary tables are they deleted after closing the connection or do I
> have to take care of that in my code?[/color]

Temporary tables disappear when you close your connection. Nevertheless,
you might find it useful (and professional!) to deliberately DROP the temp
table when you are done. If you try to re-execute your query inside the
same connection, you will find that the original table you created standing
in the way when you try to CREATE it again. By ending your code with a DROP
TABLE, you eliminate this problem.

One of the biggest learning hurdles that seems to keep people limited in
their sql coding abilities is the failure to view their sql code as a
*chain* of table transformations . TableA -> TableB -> TableC. Viewing it
in this way opens a lot of possibilities that many seem to miss. People
(myself included!) try to cram everything into a single, impossible to
decipher, statement. I blame MySQLs lack of stored procedures for this
situation. However, temporary tables provide an excellent remedy once you
discover how to use them.

Thomas Bartkus

Question on a query to find all possible combinations of a set

Question on a query to find all possible combinations of a set

Comment

Comment

Comment