From: Date: March 24 2004 7:00am
Subject: Re: Problem Query EXISTS and IN
List-Archive: http://lists.mysql.org/mysql/162387
Message-Id: <40612412.5040003@verizon.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit


webmaster@stripped wrote:
> Hi
> 
> (Sorry by my english, i speak spanish)
> 
> I have problem with a Query.
> 
> It does not recognize EXISTS nor IN nor NOT IN
> 
> My version is MySQL 4.0.11a-gamma
> 
> But I do not know if it recognizes those clauses.
> 
> I have dealed with the two forms:
> 
> 1.  SELECT usu_id FROM usuario WHERE NOT EXISTS (SELECT * FROM grupo WHERE
> usuario.g_id=grupo.g_id AND grupo.g_id > 5)
> 
> 2.  SELECT usu_id FROM usuario WHERE g_id NOT IN (SELECT g_id FROM grupo WHERE
> grupo.g_id > 5)
> 
> 
> Thanks by your help.
> 
> Juan

MySQL understands IN and NOT IN.  The problem is that you need at least 
mysql 4.1.x for subqueries, and thus for EXISTS.  You can, however, rewrite 
your queries to use a JOIN instead of a subquery -- something like this:

   SELECT usu_id
   FROM usuario
   LEFT JOIN grupo
   ON usuario.g_id=grupo.g_id AND grupo.g_id > 5
   WHERE grupo.g_id IS NULL;

See the manual for more

   <http://www.mysql.com/doc/en/Rewriting_subqueries.html>

Michael

P.S.  No need to apologize -- your English is much better than my Spanish.