"Andy Hall" <[EMAIL PROTECTED]> wrote: > > 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 query should return no rows, because if you retrieve rows where sales.sale_id is NULL, customer_id for these rows also will be NULL, not 10. > 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 can rewrite the initial query as: SELECT products.* FROM products, sales LEFT JOIN sales ss ON products.product_id=ss.product_id AND sales.product_id=ss.product_id WHERE sales.customer_id=10 AND ss.product_id IS NULL -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
