Thanks, I just found that relevant section in the manual, too.  I wish
it was clearer what version the manual documents -- I am using (almost)
the latest stable release, and I should expect that the manual documents
that.


-----Original Message-----
From: Michael Stassen [mailto:[EMAIL PROTECTED] 
Sent: Friday, June 18, 2004 2:38 PM
To: Anton Ivanov
Cc: [EMAIL PROTECTED]
Subject: Re: EXISTS/NOT EXISTS

Subqeries require mysql 4.1.

The manual offers some suggestions on rewriting subqueries as JOINs 
<http://dev.mysql.com/doc/mysql/en/Rewriting_subqueries.html>.

In your case, you want something like:

   SELECT product FROM products p
   LEFT JOIN products_by_product_areas a ON p.product = a.product
   WHERE a.product IS NULL;

Michael

Anton Ivanov wrote:

> Hi,  I'm trying to figure out how to apply these from the manual, but
to
> no avail.  Is it possible that my version (4.0.18) does not implement
> these?
> I have two tables: products and products_by_product_area.  Both have a
> field "product".  I try
> 
> SELECT product from products WHERE NOT EXISTS (SELECT DISTINCT * from
> products_by_product_areas WHERE products_by_product_areas.product =
> products.product);
> 
> Both of these queries run fine on their own.  It looks to me that I'm
> simply adapting from the manual, but all I get is
> 
> ERROR 1064: You have an error in your SQL syntax.  Check the manual
that
> corresponds to your MySQL server version for the right syntax to use
> near 'EXISTS (SELECT DISTINCT * from products_by_product_areas WHERE
> 
> OK, so I grab the example verbatim from the manual:
> SELECT DISTINCT store_type FROM Stores
>   WHERE EXISTS (SELECT * FROM Cities_Stores
>                 WHERE Cities_Stores.store_type = Stores.store_type);
> 
> And run it.  Same error.  Never mind that I don't have these tables:
the
> query does not compile.  What is going on?
> 
> 


Reply via email to