instructor allocation!
-
Hello everybody, i have 3 tables Student, Instructor, and Allocation. I have 5 students and 2 instructors how can I allocate 2 instructors to 5 students for project supervision. StudentId StudentName 1 Learner 2 Paul 3 Griffin 4 Inese 5 Kelly InstructorId InstructorName 1 Sharma 2 Bill this is out put table AllocationId InstructorName StudentName 001 Sharma Learner 002 Sharma Paul 003 Bill Griffin 004 Bill Inese 005 where the Kelly will go? please any body could give me a clue how should I do it either with queries or mathematical calculations. let suppose if I write a stored procedure 'Allocation' which take two inputs students and instructors like Allocation 5,2 how it will work. thnx in adv for any kind help. regards learner
-
Hello everybody, i have 3 tables Student, Instructor, and Allocation. I have 5 students and 2 instructors how can I allocate 2 instructors to 5 students for project supervision. StudentId StudentName 1 Learner 2 Paul 3 Griffin 4 Inese 5 Kelly InstructorId InstructorName 1 Sharma 2 Bill this is out put table AllocationId InstructorName StudentName 001 Sharma Learner 002 Sharma Paul 003 Bill Griffin 004 Bill Inese 005 where the Kelly will go? please any body could give me a clue how should I do it either with queries or mathematical calculations. let suppose if I write a stored procedure 'Allocation' which take two inputs students and instructors like Allocation 5,2 how it will work. thnx in adv for any kind help. regards learner
usually, when creating this sort of association you use the ID fields from Student and Instructor table. But you've answered your own question - If Kelly's instructor is Bill then: 001 Sharma Learner 002 Sharma Paul 003 Bill Griffin 004 Bill Inese 005 Bill Kelly
-
usually, when creating this sort of association you use the ID fields from Student and Instructor table. But you've answered your own question - If Kelly's instructor is Bill then: 001 Sharma Learner 002 Sharma Paul 003 Bill Griffin 004 Bill Inese 005 Bill Kelly
thnx for your response actually it is my fault that i didn't explain properly. Allocation table is only the format that how i need to display it but I'm not sure how can I get this format as (Allocation Table) cos I got double minded that I should use joins or should i do mathematical calculation first like this create proc AllocatIns as Declare @NumOfRec int,@NumOfRe int, @Result float select @NumOfRec= count(*) from Student select @NumOfRe= count(*) from Instructor Print @NumOfRec Print @NumOfRe select @Result =@NumOfRec % @NumOfRe Print @Result if you give me the idea how to allocate instructor to students that would be helpful. cos it is all automated process no need for operator to allocate one by one. as we do in GUI Number of students should be assigned from dropdownlist. may be I m not thinking in right direction. thanx in adv. regards learner
-
thnx for your response actually it is my fault that i didn't explain properly. Allocation table is only the format that how i need to display it but I'm not sure how can I get this format as (Allocation Table) cos I got double minded that I should use joins or should i do mathematical calculation first like this create proc AllocatIns as Declare @NumOfRec int,@NumOfRe int, @Result float select @NumOfRec= count(*) from Student select @NumOfRe= count(*) from Instructor Print @NumOfRec Print @NumOfRe select @Result =@NumOfRec % @NumOfRe Print @Result if you give me the idea how to allocate instructor to students that would be helpful. cos it is all automated process no need for operator to allocate one by one. as we do in GUI Number of students should be assigned from dropdownlist. may be I m not thinking in right direction. thanx in adv. regards learner
You've still provided a shockingly bad description of your problem and your naming of variables does not help! I think you want to allocate any given number of students to an instructor. If that is right the process is easy. Insert x number of students who have not been allocated an instructor. I have assumed you hgave changed the Allocation table to hold the StudentId and InstructorId as already suggested. I have also assumed that AllocationId is an identity field.
CREATE PROC AllocateInstructor
@numStudents INT,
@InstructorId INT
AS
INSERT INTO Allocation (InstructorId, StudentId)
SELECT TOP @numStudents
@instructorId,
studentId
FROM Students
WHERE studentId NOT IN(SELECT studentId FROM Allocation) -
You've still provided a shockingly bad description of your problem and your naming of variables does not help! I think you want to allocate any given number of students to an instructor. If that is right the process is easy. Insert x number of students who have not been allocated an instructor. I have assumed you hgave changed the Allocation table to hold the StudentId and InstructorId as already suggested. I have also assumed that AllocationId is an identity field.
CREATE PROC AllocateInstructor
@numStudents INT,
@InstructorId INT
AS
INSERT INTO Allocation (InstructorId, StudentId)
SELECT TOP @numStudents
@instructorId,
studentId
FROM Students
WHERE studentId NOT IN(SELECT studentId FROM Allocation)your assumption is absolutely fine. let me try to explain this time may be better this time we have two table student and instructor number of records can be change of both student and Instructor tables lets suppose Number of students Number of Instructor 30 11 42 9 21 13 16 5 1-what we need to do we have to allocate 11 instructor to 30 students. 2- 9 instructor to 42 students. 3- 13 instructor to 21 students. 4- 5 instructor to 16 students. let suppose 1,2,3,and 4 represents 2001,2002,2003,2004. every year number of students and instructor changes and we have to allocate instructor to new students. any way I really appreciate your patience and time you gave to understand my requirements. regards learner.