NOT IN alternative

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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

Quoted text here. Click to load it

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.

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

Quoted text here. Click to load it

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.

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 :)

Site Timeline