Bob,
There's some discussion of it at
http://dev.mysql.com/doc/refman/5.0/en/rewriting-subqueries.html.
PB
-----
Bob Gailer wrote:
Michael Stassen wrote:
Bob Gailer wrote:
Peter Brawley wrote:
Grant,
>If I want to select all the products that are in the
product_table, >but not in the sale_table, how to make the query?
The product_table >has all the products, but the sale table is a
subset of the product_table.
SELECT * FROM product_table p
LEFT JOIN sale_table s USING (prod_id)
WHERE s.prod_id IS NULL;
I have not tested that but I don't think it will work. Try:
Why not? This is the classic LEFT JOIN solution. It will work in
all versions of mysql.
Oops. I'm red-faced. Good humbling for my first appearance on this list.
It can only get better? This is a good lesson in SQL for me. I did not
know that a where clause could apply to rows in the result.
I haver searched in vain to find a clear definition of WHERE that
explains this behavior. Any pointers?
SELECT item_name FROM product_table WHERE prod_id not in (select
prod_id from sale_table);
This will work only in mysql 4.1+, and will almost certainly be
slower, because mysql's optimizer tends to treat the subquery as
dependent, meaning it will be rerun for each and every row of the
product_table.
Michael
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.6/257 - Release Date: 2/10/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
