Re: need help with SQL query

"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...

> I need help extracting data from three seperate tables.
> The Tables look like so:
>
> Courses Table:
> COURSE_ID | SUBJECT_TITLE | DESCRIPTION
>
> Scheduled_Courses Table:
> SCHEDULE_ID | START_DATE | END_DATE | COURSE_ID | INSTRUCTOR_ID
>
> Enroll Table:
> SCHEDULE_ID | ENROLL_DATE | STUDENT_ID|
>
> What I want to obtain are the ENROLL.SCHEDULE_ID and COURSES.SUBJECT_TITLE
> fields when passing the student id to the ENROLL table. The fields named
> with an "ID" suffix are just primary and foreign keys. The objective is to
> find out which scheduled courses a particular student is NOT already
> enrolled in.
>
> I attempted the following (which doesn't work):
> mysql>SELECT S.SCHEDULE_ID, C.SUBJECT_TITLE FROM COURSES AS C,
> SCHEDULED_COURSES AS S INNER JOIN ENROLL AS E ON
> (S.SCHEDULE_ID=E.SCHEDULE_ID AND
> S.COURSE_ID=C.COURSE_ID) WHERE E.STUDENT_ID=? AND E.SCHEDULE_ID IS NULL;
>
> What do I need to change to make this work?

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


.

Relevant Pages

  • need help with SQL query
    ... I need help extracting data from three seperate tables. ... Courses Table: ... fields when passing the student id to the ENROLL table. ...
    (comp.lang.java.databases)
  • Entering Data
    ... I am working on a school database that has many courses and students. ... It was working fine until I realized that some students want to enroll into ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Query question
    ... >I have created Training Database. ... First table contain the employees information such ... >courses that every employee must take and it's fields are ... >taken to enroll him in them. ...
    (microsoft.public.access.queries)