Creating team match ups using unique combinations

[RonBon]

I need to create unique team versus team match ups for tournaments that I hold.

For a given number of teams I need to find a given number games.

Example:

10 Teams 5 games each would mean each team would play games against 5 unique opponents.

I can load a table with unique combinations but struggle with the logic when it comes to picking each team's match ups. What happens is you run out of match ups when you loop through the table picking match ups because teams get used up in a linear fashion and by the time you reach the higher numbered teams there are not enough unique combos left.

As a human, I can recognize this, say in a spreadsheet, and anticipate it thus "saving" some earlier teams for match ups later.

Example:

1 vs. 2
1 vs. 3
1 vs. 4
1 vs. 9
1 vs. 10

Instead of:

1 vs. 2
1 vs. 3
1 vs. 4
1 vs. 5
1 vs. 6

So I am looking for help on how to search a table of match ups and selecting a given number for each team and having enough match ups for each team available.

Good luck and thanks in advance.

[ukfusion]

Not sure if I fully understand.

Are you wanting to select them all in one go and arrange the match ups then? or save some for later and select them then?

If you're matching them all up in one go can you not store the matched values in an array so when you come to create your next matchup you know who has already been selected?

or if you want to store the matched values you could store them like
Team 1 - team 2 - game date
1 2 xx/xx/xxxx

Then count how many games that team has been matched on and use that to only query the ones that have games left to play?

you can always run a scheduled task to clear the table once you're done or something like that.

Sorry but im not 100% sure how you plan to implement it so its a bit hard to comment.....im not an sql expert either but seeing as how no one else had offered any suggestions though id have a go....im still waiting for a reply on my question with 60 views and no reply lol.

[RonBon]

Yes, I'm storing the match ups in a table.

What I have so far is I collect the number of desired teams and number of games for each team. 10 teams and 5 games each in this example.

I loop through and create a table of unique match ups regardless of the number of games.

It looks something like this:

Col 1 is Match
Col 2 is Home
Col 3 is Away

Table Data:
1v2 1 2
1v3 1 3
1v4 1 4
..

notice no 2v1 or 2v2, data validation before write to table eliminates these duplicates.

2v3 2 3
2v4 2 4
..
10v1 10 1
10v2 10 2
etc.

I know that based on the formula for unique combinations that I will end up with 45 unqiue combinations in my table.
N(N-1)/2 = 10(9)/2 = 45

What I now need to do is pick 5 of those combinations for each team with out any team having more than 5 games.

If I just pick the first 5 for each home team I will run out of vaild or non-duplicate choices for the higher numbered teams.

Cycling through and picking just one for each team in a round robin format does not work either.

The way I achieve it in a spreadsheet is to save matchups from one or more of the earlier home teams and use them as away teams in the later team's matchups, but I can't seem to find a calculation that will tell me how many to save.

"Forest for the Trees" maybe at this point.

Thanks for your reply

[Rabbit]

Given a distinct source of teams, if you cross join it with itself and limit by the second table's team id being larger than the first table's team id, then you get a resulting set of unique combinations.

SELECT T1.TeamID, T2.TeamID
FROM (SELECT DISTINCT TeamID FROM TeamTable) T1,
     (SELECT DISTINCT TeamID FROM TeamTable) T2
WHERE T2.TeamID > T1.TeamID

Now that you have a distinct list of matchups, creating a column of random numbers will allow you to select the matchups at random. Provided that you give RAND a proper seed.

SELECT T1.TeamID, T2.TeamID, RAND(((DATEPART(mm, GETDATE()) * 100000)
    + (DATEPART(ss, GETDATE()) * 1000)
    + DATEPART(ms, GETDATE()))
    * T1.TeamID * T2.TeamID * (T1.TeamID + T2.TeamID)) AS R
FROM TeamTable T1, TeamTable T2
WHERE T2.TeamID > T1.TeamID
ORDER BY R

With that key piece of information in place. The final step would just be to use a subquery to select the top 5 for each team.