Derek Fountain wrote:
>> select distinct programme_courses.course_id from
>> programme_courses where
>>
>> programme_courses.course_id
>> not in (select course_id from courses);
>
> That doesn't make any difference!

It's something to do with primary keys. I wrote this standalone test:

---
create table t1 ( course_id int(10) signed not null, primary key
(course_id) );
create table t2 ( course_id int(10) unsigned not null, primary key
(course_id) );

insert into t1 values
(1),(-1),(-2),(2),(3),(4),(5),(6),(7),(8),(65),(66),(67),(68) ;
insert into t2 values (65),(66),(67);

select distinct course_id from t1 where course_id not in (select
course_id from t2);

drop table t1;
drop table t2;
---

which demonstrates the problem much as I have it set up in my real DB.
Replace the subquery with 65,66,67 and it works.

However, it also works if you remove the primary key clause from t2. If
you just remove the primary key from t1 the result comes back as 1.

<scratches head>

This is MySQL 4.1.13 running on SUSE Linux 10.0.