* 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
