Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2010
    Posts
    21

    Post Unanswered: Assigning dataset Equally

    Hi all,
    I need help on the SQL query. I have this kind of data which I need to assign those students to their class teacher equally. I'll show you some sample for better understanding. Below are the sample of data I have.

    Table Student

    Code:
    class  |    student
    -------+------------
    1A     |  Timmy
    -------+------------
    1A     |  Kate
    -------+------------
    1A     |  John
    -------+------------
    1A     |  Mary
    -------+------------
    1B     |  Tom
    -------+------------
    1B     |  Susan
    -------+------------
    1B     |  Will
    -------+------------
    1B     |  Amanda
    -------+------------
    1B     |  Irene
     :	     :
     :	     :
    Table Teacher

    Code:
    class  |  teacher
    -------+------------
    1A     |  Mr. Ting
    -------+------------
    1A     |  Ms. Carol
    -------+------------
    1B     |  Mr. Charles
    -------+------------
    1B     |  Mr. Ali
    -------+------------
    1B     |  Ms. Diana
     :	     :
     :	     :
    I have this two table, student and teacher. For class 1A, there are 4 students and 2 teachers should be assigned equally, which means each teacher has 2,2. Also for class 1B, 5 students should be assigned to 3 teachers, which means each teacher has 2,2,1. Below is the expected result:

    Expected output

    Code:
    class  |  student   |  teacher
    -------+------------+-------------
    1A     |   Timmy    |  Mr. Ting
    -------+------------+-------------
    1A     |   Kate	    |  Mr. Ting
    -------+------------+-------------
    1A     |   John	    |   Ms. Carol
    -------+------------+-------------
    1A     |   Mary	    |   Ms. Carol
    -------+------------+-------------
    1B     |   Tom	    |  Mr. Charles
    -------+------------+-------------
    1B     |   Susan|   Mr. Charles
    -------+------------+-------------
    1B     |   Will	    |   Mr. Ali
    -------+------------+-------------
    1B     |   Amanda   |   Mr. Ali
    -------+------------+-------------
    1B     |   Irene    |  Ms. Diana
     :	     :			:
     :	     :			:
    I need help since I never deal with many-to-many situation.
    Thanks in advance.

    Nbtet.

  2. #2
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    Inelegant solution.. but it gets there in the end.

    Uses windowed functions and modulus to work out a teacher number.

    I'm sure someone out there more skilled than me can come up with something neater and more efficient using a tenth of the number of lines... but here is my suggestion anyway.

    Code:
    select	student.class,
    		student.student,
    		teacher.teacher
    from	(
    			select	student.class,
    					student.student,
    					1+ROW_NUMBER() over (partition by student.class order by student.student)%teacher_count.teacher_count as teacher_number
    			from	student
    			join	(select	class,COUNT(*) AS teacher_count from teacher group by class) as teacher_count
    				on	teacher_count.class = student.class
    		) student
    join	(
    			select	teacher.class,
    					teacher.teacher,
    					ROW_NUMBER() over (partition by teacher.class order by teacher.teacher) as teacher_number
    			from	teacher
    		) teacher
    	on	teacher.class = student.class
    	and	teacher.teacher_number = student.teacher_number

  3. #3
    Join Date
    Jul 2010
    Posts
    21
    Thanks EngadaSQL. U somehow help me with your query.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •