NOT IN alternative

Firstly, I know there's a lot of old posts on this topic but none seem
to be relevant to my problem (selecting from a single table rather than
several different tables)...

I have a table with a user identifier (uID) and a movie identifier
(movID) and need to select the movie identifiers that appear in the
table for uID-1 but not for uID-2, using an alternative to NOT IN as it
isn't supported by the version of MySQL that I'm using.

I'd be grateful if anyone could help.

Re: NOT IN alternative

wrote in message
news:1143217047.078367.185300@i40g2000cwc.googlegroups.com.. .
> I have a table with a user identifier (uID) and a movie identifier
> (movID) and need to select the movie identifiers that appear in the
> table for uID-1 but not for uID-2

You can use an outer join with a self-join to do this. The important part
is to put the uID=2 condition in the ON clause, not the WHERE clause.

SELECT m1.movID
FROM myMovieTable AS m1 LEFT OUTER JOIN myMovieTable AS m2
ON m1.movID = m2.movID AND m1.uID = 1 AND m2.uID = 2
WHERE m2.movID IS NULL

> using an alternative to NOT IN as it
> isn't supported by the version of MySQL that I'm using.

I know what you mean here, but for what it's worth, NOT IN actually is
supported by MySQL 4.0. You can do "NOT IN (1, 2, 3)". It's subqueries
that aren't supported until MySQL 4.1.

Regards,
Bill K.

Re: NOT IN alternative

Ah. This was returning a movID for all the movies in the table but I
moved the "AND m1.uID=1" down into the where condition and it seems to
have done the trick.

Thanks for pointing me in the right direction :)