Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- NOT IN alternative
March 24, 2006, 4:17 pm
rate this thread
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
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
WHERE m2.movID IS NULL
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.
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum