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.
