p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Server 2000 (http://p2p.wrox.com/forumdisplay.php?f=20)
-   -   multi-multi-multiple Insert with subquery??? (http://p2p.wrox.com/showthread.php?t=42133)

jmjyiannis May 4th, 2006 12:48 PM

multi-multi-multiple Insert with subquery???
 
Hello, experts, friends and masters.
I get into the point!

I have a huge (for me) problem.
I have some tables in a database. Lessons, Students, Teachers, Courses and AttendedLessons.
The last table (attendedLessons) has the following collumns:
AttendedLessonID
LessonID,
StudentID,
Mark,
CourseID,
TeacherID.
I want to create an insert query which will take the courseID and the StudentID from the user and it will insert in the AttendedLessons records for all of the Lessons of this specific Course of this specific Student of a selected Teacher.

I have tried this....
insert into AttendedLessons (lessonID,CourseID,StudentID,TeacherID)
(select lessonID,CourseID,StudentID,TeacherID from

(select lessonID, CourseID from Lesson where CourseID='(anything that user gives)'),
(studentID='(anything that user gives)'),
(TeacherID='(anything that user gives)');


but Many different problems occured.

Please...... any suggestion?????


jmjyiannis May 5th, 2006 02:20 PM

Solution:

Just in case that anyone is in the same position in which I was..... the Solution comes from a multiple insert-select subquery:

   cmdSelect = New SqlCommand("insert into attendedLessons (LessonID,StudentID,TeacherID,CourseID)
(select distinct lessonID, studentid, PersonID, courseid from Lesson, student, Personnel where Courseid='" & Request.QueryString("CourseID") & "' and studentID='" & ID & "' and PersonID='" & InstructorID & "')", conn)

The result will be to insert as many records the table "lesson" has with the courseID foreignKEY =Request.QueryString("CourseID") with the ID of the student and the ID of the teacher.




All times are GMT -4. The time now is 09:45 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.