SQL query problem

[Alan Shiers]

Hi there,

I have an sql query that needs fixing. Given the two tables ENROLL AND SCHEDULED_COURSES with the following fields:

ENROLL table:
ENROLL_DATE | STUDENT_ID | SCHEDULE_ID

SCHEDULED_COURSES table:
SCHEDULE_ID | START_DATE | END_DATE | COURSE_ID

I can run the following query to get those courses a particular student is enrolled in (which is fine):

mysql> SELECT E.SCHEDULE_ID,SC.COURSE_ID FROM ENROLL AS E,SCHEDULED_COURSES AS SC WHERE E.COMPANY_ID=1 AND E.STUDENT_ID=50 AND E.SCHEDULE_ID=SC.SCHEDULE_ID;
+-------------+-----------+
| SCHEDULE_ID | COURSE_ID |
+-------------+-----------+
| 21 | 46 |
| 17 | 39 |
| 18 | 41 |
| 27 | 54 |
+-------------+-----------+

Now, what I really need is a query statement that will show those courses this student is NOT enrolled in yet. I've tried everything under the sun, but nothing seems to work.

Please advise,

Alan