Re: SubQueries and IN

[Sebastian Tobias Mendel genannt Mendelsohn]

Andy Hall wrote:

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.
subqueries requieres 4.x

try

SELECT product_id, name, description
FROM products
LEFT JOIN sales
ON products.product_id = sales.product_id
WHERE NOT sales.customer_id = 10

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)
this seems a bit different then this before

but should work, does

SELECT *
FROM sales
WHERE sales.customer_id = 10
AND sales.sale_id IS NULL
return any results?


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!


--
Sebastian Mendel
www.sebastianmendel.de
www.tekkno4u.de
www.nofetish.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]