Sebastian Tobias Mendel genannt Mendelsohn <[EMAIL PROTECTED]> wrote:
>>>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 ro
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
"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 WHE
> >
> 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 WHERE
> sales.sale_id IS NULL AND
> sales.customer_id = 10
>
I lied
Thanks for the query suggestions, but unfortunately none of them seem to do
the trick.
Not possible to upgrade to 4.x at the moment, so I am going to have to do it
in 2 queries; one to get the list of ID's, then create a list in PHP and
drop it into the second query.
Thanks for the help!
Andy Ha
* 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.
> sub-queries, but also seen examples of it done. Is it only supported in a
> later version? We are running v. 3.23.3.
As far as I know, subqueries are only supported in MySQL v4(.1?)+
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http:/
Andy:
Sub queries are supported as of version 4.1 (see link #1). As for your
query, double-check the syntax in the select piece. Specifically take out
the 'sales.sale_id' and anything else from the 'sales' table. Then try
again.
Regards,
Adam
Link #1 - http://www.mysql.com/doc/en/ANSI_diff_Subqu
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 al
