"Alan Shiers via JavaKB.com" wrote...

Before I begin, I just want to suggest that you try to use a better suited
forum for this type of questions. Even though it has with databases to do,
it has nothing to do with Java (apart for the "possible" use of the question
mark in a PreparedStatement).

Hence there are better groups suited for this, e.g. mysql-groups.

Anyway...

Look really close to the end of the last line...

 AND E.SCHEDULE_ID IS NULL;

Pure logic tells me that there will be *no* lines that fullfil that
requirement *and* are joinable with the rest of the conditions...

In the case above you can skip the "inner joining" syntax, as INNER is the
default join-type in any SQL-standard-compliant database.

With the simplest type of joining, the WHERE-clauses, your statement is
equal to the following:

SELECT S.SCHEDULE_ID, C.SUBJECT_TITLE
FROM   COURSES AS C, SCHEDULED_COURSES AS S, ENROLL AS E
WHERE  S.SCHEDULE_ID = E.SCHEDULE_ID
AND    S.COURSE_ID   = C.COURSE_ID
AND    E.STUDENT_ID  = ?
AND    E.SCHEDULE_ID IS NULL

Depending on what version of MySQL you're using, it's more or less compliant
to the SQL-standard.

There are at least three possible solutions to your problem, but they may or
may not be workable in your version of MySQL.

1. Creating two UNION-compatible sets, and make
  use of the Difference-operator, if present.
  In Standard-SQL it would look something like...

SELECT S.SCHEDULE_ID, C.SUBJECT_TITLE
FROM   COURSES AS C, SCHEDULED_COURSES AS S
WHERE  S.COURSE_ID   = C.COURSE_ID
  EXCEPT
SELECT S.SCHEDULE_ID, C.SUBJECT_TITLE
FROM   COURSES AS C, SCHEDULED_COURSES AS S, ENROLL AS E
WHERE  S.SCHEDULE_ID = E.SCHEDULE_ID
AND    S.COURSE_ID   = C.COURSE_ID
AND    E.STUDENT_ID  = ?

2. Use subqueries.

SELECT S.SCHEDULE_ID, C.SUBJECT_TITLE
FROM   COURSES AS C, SCHEDULED_COURSES AS S
WHERE  S.COURSE_ID   = C.COURSE_ID
AND    E.SCHEDULE_ID NOT IN
 (SELECT SCHEDULE_ID
  FROM   ENROLL
  WHERE  E.STUDENT_ID  = ? )

3. And Finally, what you probably are looking for.
  Use an OUTER JOIN instead of INNER JOIN... ;-)

As an OUTER JOIN combines the rows *even* when they don't match, the columns
for the other table in the non-matching row *then* are NULL, and can be
tested for that...

// Bjorn A