0

I want to write a query that will distribute records from one table to another table that take equally number depending in status.

For example, I have two tables, Students and Teachers, which have different courses (status), and I want to assign Students to each Teacher in different courses.

Students: 

name
--------+
Adam    |
Bennet  |
Elmer   |
Justin  |
Louis   |
Noah    |
Jack    |

Teachers:

name     | coerces 
---------+---------
Leo      |  1
Kyle     |  1
Ethan    |  1
Leo      |  2
Kyle     |  2
Ethan    |  2

Notice:

  1. One student cannot have more than one teacher.

  2. Each student in course 1 must have a different teacher in course 2.

  3. Each teacher should have the same number of students as any other teacher...

  4. ...Unless the number of students is not an exact multiple of the number of teachers, in which case students must be as evenly divided between teachers as possible. For instance, if we have seven students and three teachers the first two teachers will take two students and the last one will take three students.

Results: 

Student | Teacher | coerces 
--------+---------+---------
Adam    | Leo     |  1
Bennet  | Leo     |  1
Elmer   | Kyle    |  1
Justin  | Kyle    |  1
Louis   | Ethan   |  1
Noah    | Ethan   |  1
Jack    | Ethan   |  1
Louis   | Leo     |  2
Noah    | Leo     |  2
Jack    | Kyle    |  2
Adam    | Kyle    |  2
Bennet  | Ethan   |  2
Elmer   | Ethan   |  2
Justin  | Ethan   |  2

How can I do that in Oracle SQL?

MarredCheese
  • 17,541
  • 8
  • 92
  • 91
Naser AlRashdi
  • 43
  • 5
  • The simplest option is to search Stack Overflow, as it appears that this is a frequent homework assignment these days. – Littlefoot Mar 05 '19 at 06:08
  • You asked the [same question](https://stackoverflow.com/q/54880388) a week ago and accepted the answer. While I didn't check if the answer is correct, if it's not, you need to address it (there). Also, your boundary conditions aren't entirely clear (e.g. 1 contradicts 2), as slight complications (e.g. not every teacher or student has every course - and "state" sounds as if it could be the case) can make it a [very complicated problem](https://stackoverflow.com/q/2177836). If it's homework, there is probably a simple solution, but you should mention it, since otherwise there is most likely not. – Solarflare Mar 05 '19 at 06:45
  • thanks for you comment .. yes am ask same question but in different way because i face a problem in reputation that student take the same teacher in both coerces and its a wrong result ,, its not homework its like a project in our work that we want to divide customer in three stage with different employee ( and i give this example about student and teacher to clarified the idea )...no need to have every student or teacher has every course it just i want to dived student for teacher in course 1 and same student i want to divide in course 2 but different teacher not the same teacher. – Naser AlRashdi Mar 05 '19 at 07:33
  • Then add the problems you ran into and the details that differ (and make it a new question) here - you just added the "also" as point 2. As mentioned: the *exact* boundary conditions are relevant. The assignment problem is hard and you will need more than a simple query, unless your *exact* boundary conditions make it easier to solve - so maybe add more complicated examples, very exact definitions or acceptable errors/simplifications (e.g: rule x can be broken; all teachers teach every course; ...). You may also want to check/ask for an algorithm on [cs](https://cs.stackexchange.com/). – Solarflare Mar 05 '19 at 09:26
  • If you do not care about an exact solution (specifically about rule 3 and 4), assigning the students randomly to teachers according to the other rules will give you one possibility. You can repeat the random assignment several times, some will be better, some will be worse (judged by rule 3/4), improving your result over time. There might be better/faster/exact algorithms depending on your boundary conditions, but in general, this method can get you quite far if a good solution is not too unlikely. – Solarflare Mar 05 '19 at 09:39
  • thanks for your hint. – Naser AlRashdi Mar 05 '19 at 09:53

0 Answers0