confusing mysql 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

confusing mysql statement ????

Post 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
User avatar
musashi
Forum Commoner
Posts: 39
Joined: Tue Jul 23, 2002 12:51 pm
Location: Santa Cruz - CA

mysql query

Post 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.
Nunners
Forum Commoner
Posts: 89
Joined: Tue Jan 28, 2003 7:52 am
Location: Worcester, UK
Contact:

Nested Queries

Post 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......
DaiWelsh
Forum Commoner
Posts: 36
Joined: Wed Jan 08, 2003 9:39 am
Location: Derbyshire, UK

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