I have a table of seven recipes, each of which needs to be assigned to a student. Each student can be assigned a maximum of one recipe, and there are more total students than total recipes, so some students will not receive any assignment.
In my table of assignments, I need to populate which recipe is assigned to which student. (In my business requirements, assignments must be a freestanding table; I cannot add a column to the recipes table).
Below is the script I am using (including for creating sample data).
I had hoped by using the NOT EXISTS clause, I could prevent a student from being assigned more than one recipe.... but this is not working because the same student is being assigned to every recipe. Any guidance on how to fix my script would be greatly appreciated. Thank you!
/* CREATE TABLE HAVING SEVEN RECIPES */
CREATE TABLE TempRecipes( Recipe VARCHAR(16) );
INSERT INTO TempRecipes VALUES ('Cake'), ('Pie'), ('Cookies'), ('Ice Cream'), ('Brownies'), ('Jello'), ('Popsicles');
/* CREATE TABLE HAVING TEN STUDENTS, i.e. MORE STUDENTS THAN AVAILABLE RECIPES */
CREATE TABLE TempStudents( Student VARCHAR(16) );
INSERT INTO TempStudents VALUES ('Ann'), ('Bob'), ('Charlie'), ('Daphne'), ('Earl'), ('Francine'), ('George'), ('Heather'), ('Ivan'), ('Janet');
/* CREATE TABLE TO STORE THE ASSIGNMENTS */
CREATE TABLE TempAssignments( Recipe VARCHAR(16), Student VARCHAR(16) );
INSERT INTO TempAssignments( Recipe, Student )
SELECT TempRecipes.Recipe, ( SELECT S1.Student FROM TempStudents S1 WHERE NOT EXISTS (SELECT TempAssignments.Student FROM TempAssignments WHERE TempAssignments.Student = S1.Student) LIMIT 1 ) Student
FROM TempRecipes;
One way you can consider is making two separate queries, make them as a derived table and assigning a unique identifier on each query that you can match against another. I think that the unique identifier can be a row number.
This suggestion is for MySQL v8+ that supports ROW_NUMBER() function (or if I'm not mistaken; on MariaDB v10.2+?). You've already established these conditions:
Let's assume that there's an additional condition:
So, both table will have basically the same query structure as such:
SELECT Student,
ROW_NUMBER() OVER (ORDER BY RAND()) AS Rn1
FROM TempStudents;
SELECT Recipe,
ROW_NUMBER() OVER (ORDER BY RAND()) AS Rn2
FROM TempRecipes;
In that query, the additional condition no.3 of "random assignment" is implemented in the ROW_NUMBER() function. If you run the query as is, you'll almost definitely get different result of row number assignment every time. If you don't wish to do so - let's say maybe you prefer to order by student/recipe name descending - then you just replace ORDER BY RAND() with ORDER BY Student DESC.
Next we'll make both queries as derived tables then join them by matching the row number like this:
SELECT *
FROM
(SELECT Student,
ROW_NUMBER() OVER (ORDER BY RAND()) AS Rn1
FROM TempStudents) a
LEFT JOIN
(SELECT Recipe,
ROW_NUMBER() OVER (ORDER BY RAND()) AS Rn2
FROM TempRecipes) b
ON a.Rn1=b.Rn2;
The reason I'm doing LEFT JOIN here is to show that there will be some student without recipe assignment. Here's the result:
| Student | Rn1 | Recipe | Rn2 |
|---|---|---|---|
| Ann | 1 | Cookies | 1 |
| Bob | 2 | Jello | 2 |
| Charlie | 3 | Pie | 3 |
| Daphne | 4 | Brownies | 4 |
| Earl | 5 | Popsicles | 5 |
| Francine | 6 | Cake | 6 |
| George | 7 | Ice Cream | 7 |
| Heather | 8 | NULL | NULL |
| Ivan | 9 | NULL | NULL |
| Janet | 10 | NULL | NULL |
If you're doing INNER JOIN then you'll not see the last 3 of the result above since they're no matching row number from the recipe table. Our last step is just adding insert command to the query like so:
INSERT INTO TempAssignments
SELECT Recipe, Student
FROM
....
Do note that this example is using random ordering therefore the result in the TempAssignments table after the insert might not be the same as the one you get while doing testing.