confusing mysql statement ????
Posted: Thu Mar 27, 2003 3:30 am
Hi all.,
I am trying to create a Mysql statememnt that combines two tables on a identical field but I want to select all the records of that field that are not in the other table.
I have table course(courseCode, courseTitle, faculty etc)
and another table student_course(courseCode, loginName, mark)
The course table holds all the courses that are taught, and the student_course holds the users and all the modules they have taken. This table is one to one so a user can appear many times one record for each course they have taken.
My code so far that doesnt work is:
$sel_mod = "SELECT course.courseCode,course.courseTitle, course.faculty, course.core, course.semester,course.Level, course.cskpercent FROM course LEFT JOIN student_course ON course.courseCode != student_course.courseCode WHERE student_course.loginName = '$user' AND course.semester = '2'";
But from due to Joins it comparing each record in turn with each record in turn and adding the course to the results when they are not equal.
Hope its clear what i am trying to do??
Thanks for your time.
Jos
I am trying to create a Mysql statememnt that combines two tables on a identical field but I want to select all the records of that field that are not in the other table.
I have table course(courseCode, courseTitle, faculty etc)
and another table student_course(courseCode, loginName, mark)
The course table holds all the courses that are taught, and the student_course holds the users and all the modules they have taken. This table is one to one so a user can appear many times one record for each course they have taken.
My code so far that doesnt work is:
$sel_mod = "SELECT course.courseCode,course.courseTitle, course.faculty, course.core, course.semester,course.Level, course.cskpercent FROM course LEFT JOIN student_course ON course.courseCode != student_course.courseCode WHERE student_course.loginName = '$user' AND course.semester = '2'";
But from due to Joins it comparing each record in turn with each record in turn and adding the course to the results when they are not equal.
Hope its clear what i am trying to do??
Thanks for your time.
Jos