"Andy Hall" <andy@stripped> 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
/ /|_/ / // /\ \/ /_/ / /__ Egor.Egorov@stripped
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
