Page 1 of 1

confusing mysql statement ????

Posted: Thu Mar 27, 2003 3:30 am
by josboogz
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

mysql query

Posted: Thu Mar 27, 2003 12:38 pm
by musashi
Well the problem is this:

Code: Select all

course.courseCode != student_course.courseCode
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.

Nested Queries

Posted: Fri Mar 28, 2003 3:18 am
by Nunners
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......

Posted: Fri Mar 28, 2003 11:06 am
by DaiWelsh
I usually do the subquery, loop through the result building a comma seperated list of uids then do query 2 with uid IN ($list). As long as you handle empty list and the table is quick to access on uid that seems to work fine, but I expect someone has a better answer ;)