Re: Monkeys and Banannas (UPDATE .. JOIN .. SET .. GROUP)

Yes, good keys are crucial but I try to avoid them when demonstrating temp tables (I think it blurs the issue). All you need to do to have keys on a temp table is to either add the KEY() definitions to your CREATE TEMPORARY TABLE statements like this: CREATE TEMPORARY TABLE tmpCount1(KEY(name))

Re: Monkeys and Banannas (UPDATE .. JOIN .. SET .. GROUP)

Answers interspersed... Laszlo Thoth <[EMAIL PROTECTED]> wrote on 10/12/2004 01:46:08 PM: > Quoting [EMAIL PROTECTED]: > > > Good, you recognize the need to perform two separate aggregates (GROUP > > BYs) and compare the separate results... In fact, you may need two temp > > tables so that you

Re: Monkeys and Banannas (UPDATE .. JOIN .. SET .. GROUP)

Quoting [EMAIL PROTECTED]: > Good, you recognize the need to perform two separate aggregates (GROUP > BYs) and compare the separate results... In fact, you may need two temp > tables so that you can (INNER / LEFT) JOIN one to the other. (Yes, I can > think of a query that works without the second

Re: Monkeys and Banannas (UPDATE .. JOIN .. SET .. GROUP)

Good, you recognize the need to perform two separate aggregates (GROUP BYs) and compare the separate results... In fact, you may need two temp tables so that you can (INNER / LEFT) JOIN one to the other. (Yes, I can think of a query that works without the second temp table but I think it's klu

Re: Monkeys and Banannas (UPDATE .. JOIN .. SET .. GROUP)

Quoting [EMAIL PROTECTED]: > > Why would you want to do that? bananacount is something you can > > calculate with a LEFT JOIN and a GROUP BY, so storing it in the DB > > would break normalization. > > This would also the first step in creating your own OLAP cube. For each > statistic, you save yo

Re: Monkeys and Banannas (UPDATE .. JOIN .. SET .. GROUP)

In article <[EMAIL PROTECTED]>, [EMAIL PROTECTED] writes: > Harold, > Yes that information is available dynamically as you described. However, I > can think of at least two situations where what he wants to do is not only > useful but an excellent optimization. I've done denormalizations like

Re: Monkeys and Banannas (UPDATE .. JOIN .. SET .. GROUP)

Harold, Yes that information is available dynamically as you described. However, I can think of at least two situations where what he wants to do is not only useful but an excellent optimization. First, he could be calculating some kind of "static" reporting. These are reports that are gener

Re: Monkeys and Banannas (UPDATE .. JOIN .. SET .. GROUP)

In article <[EMAIL PROTECTED]>, Laszlo Thoth <[EMAIL PROTECTED]> writes: > I'm trying to create a single UPDATE query to deal with the following problem: > == > -- I've got two tables: > CREATE TABLE `banannas` ( >`owner` varchar

Re: Monkeys and Banannas (UPDATE .. JOIN .. SET .. GROUP)

Quoting Michael Stassen <[EMAIL PROTECTED]>: > According to the manual , the > multi-table UPDATE syntax is > >UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...] > SET col_name1=expr1 [, col_name2=expr2 ...] > [WHERE where_definiti

Re: Monkeys and Banannas (UPDATE .. JOIN .. SET .. GROUP)

According to the manual , the multi-table UPDATE syntax is UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...] SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition] so, you can join tables, but you can't GROUP BY. You co

Monkeys and Banannas (UPDATE .. JOIN .. SET .. GROUP)

I'm trying to create a single UPDATE query to deal with the following problem: == -- I've got two tables: CREATE TABLE `banannas` ( `owner` varchar(15) NOT NULL default '' ); CREATE TABLE `monkeys` ( `name` varchar(15) default