need help a compicated select statement

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
josboogz
Forum Newbie
Posts: 12
Joined: Thu Feb 06, 2003 5:05 am

need help a compicated select statement

Post 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
craigh
Forum Newbie
Posts: 19
Joined: Sun May 19, 2002 2:50 pm

Re: need help a compicated select statement

Post 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
Post Reply