need help with sql query

[Alan Shiers]

Hi there,

I need help extracting data from three seperate tables. The Tables look like
so:

Courses Table:
COURSE_ID | SUBJECT_TITLE | DESCRIPTION

Scheduled_Cours es 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 already enrolled
in.

I attempted the following which just gives me an empty set:
mysql> SELECT ENROLL.SCHEDULE _ID,COURSES.SUB JECT_TITLE FROM
ENROLL,SCHEDULE D_COU
RSES,COURSES WHERE ENROLL.STUDENT_ ID=36 AND
SCHEDULED_COURS ES.COURSE_ID=CO URSES.COURSE_ID ;

What do I need to change to make this work?

I want to wind up with the following:
_______________ _______________ ___
SCHEDULE_ID | SUBJECT_TITLE
_______________ _______________ ___
32 | Biology 101
_______________ _______________ ___
46 | English 101
Thanks,

Alan Shiers

[Bill Karwin]

Alan Shiers wrote:

I attempted the following which just gives me an empty set:
mysql> SELECT ENROLL.SCHEDULE _ID,COURSES.SUB JECT_TITLE FROM
ENROLL,SCHEDULE D_COU
RSES,COURSES WHERE ENROLL.STUDENT_ ID=36 AND
SCHEDULED_COURS ES.COURSE_ID=CO URSES.COURSE_ID ;

Well, I see you're missing a join condition between ENROLL and
SCHEDULED_COURS ES, but that should result in _more_ records, not fewer.

I'd also write the query differently, because I prefer to use row
aliases and SQL-92 join syntax:

SELECT E.SCHEDULE_ID, C.SUBJECT_TITLE
FROM ENROLL AS E
INNER JOIN SCHEDULED_COURS ES AS S ON E.SCHEDULE_ID = S.SCHEDULE_ID
INNER JOIN COURSES AS C ON C.COURSE_ID = S.COURSE_ID
WHERE E.STUDENT_ID = ?

All I can think of is that your values for course_id in the COURSES and
SCHEDULED_COURS ES tables don't match up. Try a couple of queries to
test if you have matches:

SELECT C.COURSE_ID, S.COURSE_ID
FROM COURSES AS C
LEFT OUTER JOIN SCHEDULED_COURS ES AS S ON C.COURSE_ID = S.COURSE_ID
WHERE S.COURSE_ID IS NULL

The above should display NULL for the S.COURSE_ID if you have no
sessions scheduled for a given course. This could occur either if you
mis-entered the data for the scheduled courses, or if a given course is
not being held.

SELECT C.COURSE_ID, S.COURSE_ID
FROM COURSES AS C
RIGHT OUTER JOIN SCHEDULED_COURS ES AS S ON C.COURSE_ID = S.COURSE_ID
WHERE C.COURSE_ID IS NULL

The above should display NULL for the C.COURSE_ID if you have no course
on record for a given scheduled course. I.e. the course_id of the
scheduled one does not exist. I assume this should _not_ happen, so it
would indicate a data integrity problem.

Now compare these two lists. Do you see any patterns of course_ids that
_should_ match up, but don't? For instance, perhaps someone entered
"XYZ123" for the course id in the courses table, but "XYZ-123" in the
scheduled_cours es table.

Regards,
Bill K.

[Alan Shiers]

Thanks Bill,
I'm afraid I'm new to SQL so I don't completely understand all this
business of INNER JOINS, LEFT OUTER JOINS, etc.
I'm totally lost when it comes to having to use these syntax. The statement
you provided certainly did work. I just don't understand why it works.
Having said that, maybe you can help me with another statement. The last
statement was used to display only those scheduled course records the
student was already enrolled in. Now, I need one that does just the
opposite. Display those scheduled courses that the student is not yet
enrolled in.

Following your lead with the aliases I came up with the followng (which
doesn't work), but it's a starting place:

SELECT S.SCHEDULE_ID, C.SUBJECT_TITLE FROM COURSES AS C, SCHEDULED_COURS ES
AS S INNER JOIN ENROLL AS E ON (S.SCHEDULE_ID= E.SCHEDULE_ID AND
S.COURSE_ID=C.C OURSE_ID) WHERE E.STUDENT_ID=? AND E.SCHEDULE_ID IS NULL;

Please advise,

Alan