* Andy Hall > 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.
That is a very old version... you should upgrade if you can. Version 4.0 is the current recommended version: <URL: http://www.mysql.com/downloads/index.html > sub-queries will be allowed from mysql version 4.1 (not yet stable): <URL: http://www.mysql.com/doc/en/ANSI_diff_Subqueries.html > See also this page, describing which versions of MySQL will support which new feature: <URL: http://www.mysql.com/doc/en/Roadmap.html > > 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! You need to get the conditions for the LEFT JOIN out of the WHERE clause: SELECT product_id, name, description, sales.sale_id FROM products LEFT JOIN sales ON products.product_id = sales.product_id AND sales.customer_id = 10 WHERE sales.sale_id IS NULL Hope this helps, -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
