Derek Fountain wrote:
> I have a table that contains these numbers in a column:
>
> mysql> select distinct course_id from programme_courses;
> +-----------+
> | course_id |
> +-----------+
> | 1 |
> | -1 |
> | -2 |
> | 2 |
> | 3 |
> | 4 |
> | 5 |
> | 6 |
> | 7 |
> | 8 |
> | 65 |
> | 66 |
> | 67 |
> | 68 |
> +-----------+
> 14 rows in set (0.00 sec)
>
> and I have these values in a different table:
>
> mysql> select course_id from courses;
> +-----------+
> | course_id |
> +-----------+
> | 65 |
> | 66 |
> | 67 |
> +-----------+
> 3 rows in set (0.00 sec)
>
> I'm looking to find those values in the first query that aren't in the
> second, so I tried this:
>
> mysql> select distinct programme_courses.course_id from
> programme_courses where course_id not in (select course_id from courses);
> +-----------+
> | course_id |
> +-----------+
> | 1 |
> +-----------+
> 1 row in set (0.00 sec)
>
> which somewhat puzzles me. If I replace the subquery with the values I
> know it returns, it works as required:
>
> mysql> select distinct programme_courses.course_id from
> programme_courses where course_id not in (65,66,67);
> +-----------+
> | course_id |
> +-----------+
> | 1 |
> | -1 |
> | -2 |
> | 2 |
> | 3 |
> | 4 |
> | 5 |
> | 6 |
> | 7 |
> | 8 |
> | 68 |
> +-----------+
> 11 rows in set (0.00 sec)
>
> Can someone explain where I'm going wrong with my subquery?

Why bother with a subquery:

SELECT DISTINCT `programme_courses`.course_id
FROM `programme_courses`
LEFT JOIN `courses` USING ( course_id )
WHERE `courses`.course_id IS NULL