On 6/12/06, Barry <[EMAIL PROTECTED]> wrote:
Kim Christensen schrieb:
> 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
Use INNER JOIN :)
That worked great, really cut the time on my queries! However, how
would one filter out the INNER JOIN statement if it returns more than
one row to JOIN? I have reworked my previous table setup and query so
that I need the row that has the lowest value of "item_updated", not
particularly 1.
Here's what it looks like right now:
SELECT * FROM products p
INNER JOIN manufactors m ON m.manufactor_id = p.manufactor_id
INNER JOIN items i ON i.product_id = p.product_id
The problem is, that each entry in "products" may occur more than once
in "items", and they are identified by "product_id" in both tables.
How do I filter out the results from the last INNER JOIN by certain
criterias? I want the INNER JOIN to only return the row from items
which has the lowest value in the column "item_updated".
As it is right now, MySQL returns a row for each time the product
occurs in the "items" table, which is not what I want :-)
(Sorry if this is a dupe post, but I sent it from the wrong address)
--
Kim Christensen
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
