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 rows, because if you retrieve rows where sales.sale_id >> is NULL, customer_id for these rows also will be NULL, not 10. > > > you are wrong, or do you know the table-structure?
No, I don't know table structure. > sales.sale_id can be NULL while customer_id can be 10 ! Probably you misundernstood me. Look at the following example, there are 2 test table: t1 and t2. mysql> select * from t1; +------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | +------+ 5 rows in set (0.00 sec) mysql> select * from t2; +------+------+ | id | name | +------+------+ | 1 | vita | | 3 | egor | | 5 | tony | +------+------+ 3 rows in set (0.00 sec) Now I want to do this simple SELECT statement that is like author want to do: SELECT * FROM t1 WHERE t1.id NOT IN (SELECT t2.id FROM t2 WHERE name='egor'). For versions before 4.1 I can rewrite NOT IN() using LEFT JOIN. Here is the output of LEFT JOIN: mysql> select * from t1 left join t2 on t1.id=t2.id; +------+------+------+ | id | id | name | +------+------+------+ | 1 | 1 | vita | | 2 | NULL | NULL | | 3 | 3 | egor | | 4 | NULL | NULL | | 5 | 5 | tony | +------+------+------+ 5 rows in set (0.01 sec) As you can see, if I add condition t2.id IS NULL to the WHERE clause, I can't retrieve rows where name='egor'. That is why author didn't get any rows. -- 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]
