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
confusing mysql statement ????
Moderator: General Moderators
mysql query
Well the problem is this:
if you have that in a left join then you are right, it will compare and return each row on the left join that doesn't match. So you will get a flood of results back. What you really want is to do a nested query. Thing is, in MySQL I don't think they have implemented that yet. This was something that was talked about a lot (and was one of the key selling points for other pricey db's). I believe they will bring this out, but for now you might need to break up the query into two queries.
Code: Select all
course.courseCode != student_course.courseCodeNested Queries
Interesting point about nested queries... I've never really understood why MySQL haven't implemented it.
Is it not part of the Std for SQL? Do we know when they plan to implement it?
Also, what's the best way to put together a "virtual" nested query in mysql, i.e. a series of code that acts as a nested query......
Is it not part of the Std for SQL? Do we know when they plan to implement it?
Also, what's the best way to put together a "virtual" nested query in mysql, i.e. a series of code that acts as a nested query......