André Hartmann wrote:
>>How do I structure my sql statement so that I get this result?
>
>
> You probably want more than one student in a course and more than one
> course for a student. so probably you want a many-many relationship between
> TblStudent and TblCourse. You might want:
>
> TblCourse = { CourseId(PK), CourseName, CourseDate, Result }
> TblStudent = { StudentId(PK), StudentName, StudentSurname, StudentTel }
> TblStudentIsInCourse = { StudentId(FK), CourseId(FK) }
>
> Given that, you can select
>
> * all courses for a given student
>
> select * from TblCourse C, TblStudentIsInCourse SC
> where C.CourseId = SC.CourseId
> and SC.StudentId = :1
>
> * all students for a course:
>
>
> select * from TblStudent S, TblStudentIsInCourse SC
> where S.StudentId = SC.StudentId
> and SC.CourseId = :1
>
> * all students in all courses:
>
> select * from TblStudent S, TblCourse C, TblStudentIsInCourse SC
> where S.StudentId = SC.StudentId
> and C.CourseId = SC.CourseID
And if you do have a many-to-many relationship than you must create
an intersecting entity between them consisting of the primary keys
from both.
Daniel Morgan
Received on Fri Jul 23 2004 - 22:35:19 CDT
