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?

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 |

> 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?

select distinct programme_courses.course_id from
programme_courses where

programme_courses.course_id

not in (select course_id from courses);

> 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!

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.

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

Does this work?

SELECT DISTINCT c.course_id FROM programme_courses pc
RIGHT JOIN courses c ON c.course_id = pc.course_id
WHERE ISNULL(pc.course_id);

> 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

Heh, I'm rather new to this. I thought there might be smart way to do it.

However, I'm pretty sure I've uncovered a bug. There is something
similar in the MySQL bugs DB from about a year ago (#10649) which wasn't
resolved. I'm currently trying to find a more up to date MySQL
installation (> 4.1.13) to see if the issue still appears. If it does
I'll raise it as a bug.

I'm the meantime, thanks for showing me how to do it properly. )

Derek Fountain wrote:
> > 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
>
> Heh, I'm rather new to this. I thought there might be smart way to do it.
>
> However, I'm pretty sure I've uncovered a bug. There is something
> similar in the MySQL bugs DB from about a year ago (#10649) which wasn't
> resolved. I'm currently trying to find a more up to date MySQL
> installation (> 4.1.13) to see if the issue still appears. If it does
> I'll raise it as a bug.
>
> I'm the meantime, thanks for showing me how to do it properly. )

It's the way most folks are forced to do it 'cos MySQL has only just
recently (as of 4.1 I believe) started supporting subqueries! But it is
also (usually) more efficient than a subquery, particularly when good
indexes are used. See
http://www.databasejournal.com/featu...le.php/3434641 for a
discussion on it.

strawberry wrote:
> Does this work?
>
> SELECT DISTINCT c.course_id FROM programme_courses pc
> RIGHT JOIN courses c ON c.course_id = pc.course_id
> WHERE ISNULL(pc.course_id);

No because you've got the databases the wrong way round.

See my earlier post for a working version

Captain Paralytic wrote:
> strawberry wrote:
> > Does this work?
> >
> > SELECT DISTINCT c.course_id FROM programme_courses pc
> > RIGHT JOIN courses c ON c.course_id = pc.course_id
> > WHERE ISNULL(pc.course_id);
>
> No because you've got the databases the wrong way round.
>
> See my earlier post for a working version

Oops!

SELECT DISTINCT pc.course_id
FROM courses c
RIGHT JOIN programme_courses pc ON c.course_id = pc.course_id
WHERE ISNULL( c.course_id )

- or just like the man already said