Sounds like a job for a LEFT JOIN. However, before we can help, we need to know the table structure andhow the two tables are related. Of course, if sub-selects are out of the question.
-----Original Message----- From: BD [mailto:[EMAIL PROTECTED]] Sent: Monday, January 28, 2002 12:13 PM To: [EMAIL PROTECTED] Subject: How to emulate subselect on larger table? ### I have 2 tables, detail and product. The detail table could have millions of rows. The product table could have a few hundred. I need to know which products are in the detail table. (It would also be nice to know which products aren't in the detail table). The problem is it has to be fast, very fast. Since it is on a web server it can't tie up the CPU for several seconds while it needlessly returns thousands of records. I only need to know which fields from one table *exist* in the other table. Sounds simple right? Ideally it would look like: select product_id from products where prod_id in (select prod_id from detail where <somedetailwhere>); The <somedetailwhere> is an optional where clause that could be applied to the detail table. It will use indexes so it will be quite fast. Now if I try a simple join like: select prod_id from products, detail where <somedetailwhere> and product.prod_id = detail.prod_id; it will of course return duplicate prod_id's because the product could appear in tens of thousands of detail items. I don't need to return thousands of rows. I only need to return 1 row of each prod_id if that prod_id appears in the detail table. I can't use: select distinct prod_id from products, detail where <somedetailwhere> and product.prod_id = detail.prod_id; because it takes too long. It will still returns hundreds of thousands of rows unnecessarily. So is there a solution to this "simple" problem? TIA Brent --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
