Andy: Sub queries are supported as of version 4.1 (see link #1). As for your query, double-check the syntax in the select piece. Specifically take out the 'sales.sale_id' and anything else from the 'sales' table. Then try again.
Regards, Adam Link #1 - http://www.mysql.com/doc/en/ANSI_diff_Subqueries.html -----Original Message----- From: Andy Hall [mailto:[EMAIL PROTECTED] Sent: Monday, September 08, 2003 10:02 AM To: [EMAIL PROTECTED] Subject: SubQueries and IN Hi, I have just started using MySQL from MSSQL 7. I need to port the following into MySQL from an existing (working) query on MSSQL Server: SELECT product_id, name, description FROM products WHERE product_id NOT IN (SELECT product_id FROM sales WHERE customer_id = 10) i.e. get all the products that a particular customer has not already bought This errors, and I have since read that the MySQL "IN" does not allow sub-queries, but also seen examples of it done. Is it only supported in a later version? We are running v. 3.23.3. I have also tried: SELECT product_id, name, description, sales.sale_id FROM products LEFT JOIN sales ON products.product_id = sales.product_id WHERE sales.customer_id = 10 AND sales.sale_id IS NULL This does not return any records as it seems to ignoring the LEFT JOIN part when I stick on the "WHERE sales.customer_id = 10". (pretty sure this query would work in MS-SQL) There must be a way to do this, but I dont seem to be able to put my finger on it and I would appreciate any help! Thanks Andy Hall. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
