Re: SQL for spreadsheet output

From: André Hartmann <andrehartmann_at_hotmail.com>
Date: Fri, 23 Jul 2004 17:44:32 +0200
Message-ID: <41013262$1@olaf.komtel.net>

> 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 Received on Fri Jul 23 2004 - 10:44:32 CDT