JOIN query for subSELECT???

[Integr8]

Short Version of my problem:
======================
How do I do this query in MySQL 3.23.54?:

SELECT blah blah FROM table1 WHERE table1.row_id NOT IN (SELECT table2.row_id FROM table2 WHERE column = myVariable);
======================

Longer Version!:

I'm working on a form to modify course information for a school website. All the data is stored in two tables:

table1 holds course information
table2 holds prerequisite information (some courses have other courses that must be taken first.)

table2 has three fields: *prereq_id, course_id, prereq_course_id
So if a course has two prerequisites, there are two entries in table2 where course_id gets repeated and prereq_course_id identifies the two courses back in table1 that are the prerequisites.

I am using two select boxes in my update form with a bit of Java to swap items back and forth for the prerequisites. Being an update form though, I want to populate those fields with the current information.

The field on the right contains the already specified prereqs. That one's not a problem.

The field on the left needs to contain all the available courses in table1 EXCEPT the course being modified and the prerequisites already indicated for it in table2. That's where I'm stuck!

The above query would do the job just fine and is repeated here in greater detail (myVariable is the id of the course being modified):

SELECT blah blah FROM table1 WHERE table1.course_id NOT IN (SELECT prereq_course_id FROM table2 WHERE table2.course_id = myVariable);

The query was originally written by someone on a postgres db. I've tried a ton of different JOIN queries but can't seem to pin it down. Especially when more than one course has the same prerequisites.

My last attempt was:
->SELECT courses.course_id, course_dept, course_code, course_title
->FROM courses LEFT JOIN prereqs ON courses.course_id = prereqs.prereq_course_id
->WHERE courses.course_id != myVariable AND (prereqs.course_id IS NULL OR prereqs.course_id != myVariable);

This works but only if there courses are only used once as prerequisites.

Any ideas? Anyone? Anyone?

 

[shadedecho]

SUB-SELECT's are not supported until the latest (4.x+) versions of MySQL and even then not completely, so I'd suggest maybe another way would be to do an INNER JOIN on the other table with the ON clause being a <> instead of an =... so something like:

SELECT t1.blah,
       t1.blah2 
FROM table1 t1
     INNER JOIN table2 t2 ON (t1.course_id <> t2.prereq_course_id)
WHERE t2.course_id = myVariable

is this along the lines of what you are needing?

 

[swampBoogie]

SELECT blah blah 
  FROM table1 left outer join table2
    on table1.row_id = table2.row_id 
   and table2.column = myVariable
 where table2.row_id is null

 

[Integr8]

@shadedecho

Thanks. You lost me on that one though - maybe I didn't explain it well enough.

@swampBoogie

You nailed it. Freak! That was too simple. I was staring right at it! DOH! I mean it was right there in front of me! I must have just blanked out or something! All I had to do was add &quot;AND table1.row_id != myVariable&quot; to the end so the thing itself was excluded.

Thanks a pile to both of you for your help. I'm going back to graphic design! :-(