How about: select * from products p inner join manufactors m on p.manufactor_id=m.manufactor_id inner join items i on p.product_id=i.product_id and i.item_updated=1
Donna "Kim Christensen" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 06/12/2006 06:15 AM Please respond to [EMAIL PROTECTED] To mysql@lists.mysql.com cc Subject {Spam?} Limiting results from joins Hey list; Consider this statement: SELECT * FROM products p, manufactors m, items i WHERE p.manufactor_id = m.manufactor_id && p.product_id = i.product_id; Now, each unique product_id from "products" can have more than one entry in the "items" table, but I only want to fetch the one which fullfills a certain criteria. In this case, I want the statement only to JOIN the row if the column "item_updated" from the "items" table equals "1". Is this the proper way to solve this: SELECT * FROM products p, manufactors m, items i WHERE p.manufactor_id = m.manufactor_id && p.product_id = i.product_id && i.item_id = (SELECT item_id FROM items i2 WHERE i2.item_updated = 1); I find the above solution VERY slow, almost as if I have missed out on a very fundamental part of the logic - but it does get the work done. How could I speed this up, or solve it in another statement? Regards -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare.