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? > >
