Page 1 of 1

need help a compicated select statement

Posted: Sat Mar 15, 2003 10:38 am
by josboogz
Hi all,

I have been trying to write a select statement in Mysql to retrieve the right data from three tables.

TABLE course

courseCode
courseTitle
faculty
semester
level
credits
core
type
etc..

TABLE lecturer_course
courseCode
lecturerCode

TABLE lecturer
lecturerCode
lecturerName
etc..


basically i need a statement that will select the fields listed in the course table with lecturerName given a variable that holds the courseCode.

$info = "SELECT courseCode, courseTitle, faculty, core, semester, level, credits, lecturer.lecturerName FROM course, lecturer, lecturer_course WHERE courseCode = '$codebox' AND lecturer_course.courseCode = '$codebox' AND lecturer_course.lecturerCode = lecturer.lecturerCode";

am i roughly along the right lines. It doesn't work can anyone help.


Thankyou for your time,

Jos

Re: need help a compicated select statement

Posted: Sat Mar 15, 2003 12:01 pm
by craigh
josboogz wrote:basically i need a statement that will select the fields listed in the course table with lecturerName given a variable that holds the courseCode.
select course.*, lecturer.lecturerName from course left join lecturer_course on course.courseCode=lecturer_course.courseCode left join lecturer on lecturer_course.lecturerCode=lecturer.lecturerCode where course.courseCode='$codebox';

not tested