Re: Limiting results from joins

[Michael Stassen]

Kim Christensen wrote:
> 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".

Adding

  WHERE i.item_updated = 1

is the simplest solution.

> 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);

Definitely not, as you've seen.  Your subquery returns a *lot* of unwanted 
item_ids.

> 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?

[EMAIL PROTECTED] wrote:
> 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

I'd have suggested

  SELECT *
  FROM products p
  JOIN manufactors m ON p.manufactor_id = m.manufactor_id
  JOIN items i ON p.product_id = i.product_id
  WHERE i.item_updated=1

Although they appear different in theory, the optimizer will almost certainly 
treat them the same.  (Check each with EXPLAIN to see.)  In either case, the 
efficient way to execute the query will depend largely on what indexes are in 
the items table.  With an index on items.item_updated, the optimizer may choose 
to use that index to select the appropriate rows from items, then join to the 
other two tables.  On the other hand, if items has a multi-column index on 
(product_id, item_updated), the optimizer would be able to join table items 
last, using the index to match the one matching row for each row of table 
products.  Without the multi-column index, it couldn't do that.


Kim Christensen wrote:
> 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.

Well, that's different.  This is an example of why you shouldn't try to simplify 
your query for the list.  When your question doesn't include your real query, 
you are quite likely to get answers that don't apply to your real query.


> 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".

This is a FAQ, with solutions given in the manual, 
. 
 (While the subquery solution looks more elegant, the temporary table solution 
is frequently more efficient.)


> 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 :-)

Peter Lauri wrote:
> Just add:
>
> ORDER BY i.item_updated GROUP BY i.product_id
>
> (assuming that item_updated and product_id are in table i)

That definitely will NOT work.  You cannot select columns not included in the 
grouped columns and expect to get meaningful results.  See the manual for why, 
.


Kim Christensen wrote:
> That gives me the result that I'm after, the only problem seems to be
> that the sorting is
> made before the matching - on all the rows (huge execution time) - how
> can I prevent this?

I doubt that really gives the result you want, unless you were very lucky, or 
still haven't told us the real query.  In any case, this query will not work 
reliably.


Peter Lauri wrote:
> Take away the GROUP BY thing. And after that you just check if the rows are
> in the order that you want. The upper row would be the one that GROUP BY
> will take. Are you sure that you want the "lowest" value in the
> "item_update"? I would like to have the highest value.
>
> If you want the latest date, then you do ORDER BY i.item_updated DESC GROUP
> BY i.product_id

Again, no.  ORDER BY does not affect which rows are "chosen" by GROUP BY, which 
is what you are hoping for with this query.  In fact, GROUP BY does not even 
select rows.  It returns group identifiers and aggregate functi

RE: Limiting results from joins

[Peter Lauri]

Take away the GROUP BY thing. And after that you just check if the rows are
in the order that you want. The upper row would be the one that GROUP BY
will take. Are you sure that you want the "lowest" value in the
"item_update"? I would like to have the highest value.

If you want the latest date, then you do ORDER BY i.item_updated DESC GROUP
BY i.product_id

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of Kim Christensen
Sent: Monday, June 12, 2006 9:15 PM
To: Peter Lauri
Cc: MySQL List
Subject: Re: Limiting results from joins

On 6/12/06, Peter Lauri <[EMAIL PROTECTED]> wrote:
> --
>
> 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".
>
> --
>
> Just add:
>
> ORDER BY i.item_updated GROUP BY i.product_id
>
> (assuming that item_updated and product_id are in table i)

That gives me the result that I'm after, the only problem seems to be
that the sorting is
made before the matching - on all the rows (huge execution time) - how
can I prevent this?

MVH
-- 
Kim Christensen


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Limiting results from joins

[Kim Christensen]

On 6/12/06, Peter Lauri <[EMAIL PROTECTED]> wrote:

--

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".

--

Just add:

ORDER BY i.item_updated GROUP BY i.product_id

(assuming that item_updated and product_id are in table i)


That gives me the result that I'm after, the only problem seems to be
that the sorting is
made before the matching - on all the rows (huge execution time) - how
can I prevent this?

MVH
--
Kim Christensen

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Limiting results from joins

[Peter Lauri]

--

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".

--

Just add:

ORDER BY i.item_updated GROUP BY i.product_id

(assuming that item_updated and product_id are in table i)


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Limiting results from joins

[Kim Christensen]

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]

Re: Limiting results from joins

[Kim Christensen]

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 :-)

Regards
--
Kim Christensen

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Limiting results from joins

[Barry]

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 :)

Barry
--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]