I have a table called users in that I have id auto inc primary key , username , password , email , admin_level
I want to loop each username and insert them into new table called matchups
table matchups has tournament_id , player_1 , player_2. I need to split each username from previous table and have each username be assigned player_1 and player_2
end result to have a player vs player matchup as they signup
OK... Normally transferring data from one SQL table to another is better done within the SQL language itself, so let's look at how we can do that (I have never dealt with a problem like this before, so I am learning as I go).
My Test Table players, for reference, was:
`player_id` `name`
1 Robert Doe
2 John Smith
First, trying to find the potential permutations of two players.
I found a SO Question which was similar, but dealt with two tables interacting. MySQL - Show All Permutations?. MySQL can use one table twice in the same query, we just need to set aliases for each of them so they don't clash.
SELECT `p1`.`player_id` , `p1`.`name` , `p2`.`player_id` , `p2`.`name`
FROM `players` AS `p1`
CROSS JOIN `players` AS `p2`
This produced the following:
`p1`.`player_id` `p1`.`name` `p2`.`player_id` `p2`.`name`
1 Robert Doe 1 Robert Doe
1 Robert Doe 2 John Smith
2 John Smith 1 Robert Doe
2 John Smith 2 John Smith
OK... Unless people are playing against themselves, this will not do, so we better eliminate any rows where both sides are the same.
NOTE: Skip ahead to "Eliminate Self-Matches & Duplicate, but Reversed, Matches - Amended Solution" for a more efficient version of the following steps. (Older solution kept here to show my own learning through this problem.)
SELECT `p1`.`player_id` , `p1`.`name` , `p2`.`player_id` , `p2`.`name`
FROM `players` AS `p1`
CROSS JOIN `players` AS `p2`
WHERE `p1`.`player_id`!=`p2`.`player_id`
This produced the following:
`p1`.`player_id` `p1`.`name` `p2`.`player_id` `p2`.`name`
1 Robert Doe 2 John Smith
2 John Smith 1 Robert Doe
Much better. Now, this is good, and is perfect if, for instance, every player/team plays each other twice (for instance, if they have a home and an away game against each other), but if these are one-off matchups, we need to figure out how to filter out the duplicate matches.
Each member has an id number, and numbers, being, well, numbers, can be easily compared against each other and sorted. To do that compare and sort, on a per row basis, we need to use the CASE conditional. (SQL MAX of multiple columns?)
SELECT `p1`.`player_id` , `p1`.`name` , `p2`.`player_id` , `p2`.`name` ,
CASE WHEN `p1`.`player_id`<`p2`.`player_id`
THEN CONCAT( `p1`.`player_id` , '|' , `p2`.`player_id` )
ELSE CONCAT( `p2`.`player_id` , '|' , `p1`.`player_id` )
END AS `uniqueMatch`
FROM `players` AS `p1`
CROSS JOIN `players` AS `p2`
WHERE `p1`.`player_id`!=`p2`.`player_id`
Which returns
`p1`.`player_id` `p1`.`name` `p2`.`player_id` `p2`.`name` `uniqueMatch`
2 John Smith 1 Robert Doe 1|2
1 Robert Doe 2 John Smith 1|2
Now all we have to do group the rows by those matches
SELECT `p1`.`player_id` , `p1`.`name` , `p2`.`player_id` , `p2`.`name` ,
CASE WHEN `p1`.`player_id`<`p2`.`player_id`
THEN CONCAT( `p1`.`player_id` , '|' , `p2`.`player_id` )
ELSE CONCAT( `p2`.`player_id` , '|' , `p1`.`player_id` )
END AS `uniqueMatch`
FROM `players` AS `p1`
CROSS JOIN `players` AS `p2`
WHERE `p1`.`player_id`!=`p2`.`player_id`
GROUP BY `uniqueMatch`
Which returns:
`p1`.`player_id` `p1`.`name` `p2`.`player_id` `p2`.`name` `uniqueMatch`
2 John Smith 1 Robert Doe 1|2
And adding another row to the table to check it behaves itself:
`p1`.`player_id` `p1`.`name` `p2`.`player_id` `p2`.`name` `uniqueMatch`
2 Robert Doe 1 John Smith 1|2
3 Matt Citizen 1 John Smith 1|3
3 Matt Citizen 2 Robert Doe 2|3
With many thanks to David Winant, the solution could be shorted somewhat. Rather than using the CASE THEN CONCAT( ... ) ELSE ... we can apply a simple additional check to the WHERE clause to ensure that we only get one set of matched players. Again, as we know that the player's IDs will be diferent, and as such one will be greater than the other, we can check that is the case to eliminate half the returned rows.
SELECT `p1`.`player_id` , `p1`.`name` , `p2`.`player_id` , `p2`.`name`
FROM `players` AS `p1`
CROSS JOIN `players` AS `p2`
WHERE `p1`.`player_id`<`p2`.`player_id`
Which returns
`p1`.`player_id` `p1`.`name` `p2`.`player_id` `p2`.`name`
1 John Smith 2 Robert Doe
1 John Smith 3 Matt Citizen
2 Robert Doe 3 Matt Citizen
Again, thank you to David Winant for identifying this amendment and sharing it with me - now I have learnt even more in helping out with this problem.
Excellent! Now, we can use these results to automatically INSERT into another table:
If we were doing it manually, we'd use something like:
INSERT INTO `matches` ( `id_player1` , `id_player2` ) VALUES ( 2 , 1 ) , ( 3 , 1 ) ...
But, we can use the above SELECT query to take care of all the VALUES bit:
INSERT INTO `matches`
( `id_player1` , `id_player2` )
SELECT `p1`.`player_id` , `p2`.`player_id`
FROM `players` AS `p1`
CROSS JOIN `players` AS `p2`
WHERE `p1`.`player_id`<`p2`.`player_id`
Which produces the table:
`id_match` `id_player1` `id_player2`
1 2 1
2 3 1
3 3 2
And, voila, you have it!
The above works from an established table of players (which is fine in instances where everyone registers before a deadline and the matchups are then calculated), but for instances where new users/players are added afterwards, the following code can be used to add the new matches.
INSERT INTO `matches` ( `id_player1` , `id_player2` )
SELECT 123 , `player_id`
FROM `players`
WHERE `player_id`!=123
Where 123 is the player_id of the newly added player.
