If you only interested in getting the overall sum from the child tables you
could try the following:
1)
Change your child create statements to:
CREATE TABLE child1
(
auto_nr int(11) unsigned zerofill NOT NULL,
link int(11) unsigned ,
value int(11),
PRIMARY KEY auto_nr(auto_nr),
KEY link1_key(link)
)
CREATE TABLE child2
(
auto_nr int(11) unsigned zerofill NOT NULL,
link int(11) unsigned ,
value int(11),
PRIMARY KEY auto_nr(auto_nr),
KEY link2_key(link)
)
2)
Then create a merge table like:
CREATE TABLE childMerge (
auto_nr int(11) unsigned zerofill NOT NULL,
link int(11) unsigned ,
value int(11),
KEY auto_nr(auto_nr),
KEY link_key(link)
) TYPE=MERGE UNION=(child1,child2);
3)
The the one statement then becomes:
Select master.link, max(dat) as m_dat, sum(childMerge.value)
from
master
left join childMerge using (link)
group by master.link
KEY is a synonym for INDEX
Regards
Dan
> -Original Message-
> From: Alvis [mailto:[EMAIL PROTECTED]]
> Sent: Friday, 22 February 2002 2:44 p.m.
> To: [EMAIL PROTECTED]
> Subject: Can I do it with single query in mysql?
>
>
> Hello all,
>
> Suppose I have 3 tables,
> 1. master (auto_nr, link, dat)
>
> CREATE TABLE master
> (
> auto_nr int(11) unsigned zerofill NOT NULL,
> link int(11) unsigned zerofill NOT NULL,
> dat DATE NOT NULL,
> PRIMARY KEY auto_nr(auto_nr),
> KEY link_key(link)
> )
> 2. child1 (auto_nr, link1, value1)
>
> CREATE TABLE child1
> (
> auto_nr int(11) unsigned zerofill NOT NULL,
> link1 int(11) unsigned ,
> value1 int(11),
> PRIMARY KEY auto_nr(auto_nr),
> KEY link1_key(link1)
> )
>
> 3. child2 (auto_nr, link2, value2)
>
> CREATE TABLE child2
> (
> auto_nr int(11) unsigned zerofill NOT NULL,
> link2 int(11) unsigned ,
> value2 int(11),
> PRIMARY KEY auto_nr(auto_nr),
> KEY link2_key(link2)
> )
>
> At the moment I use temporary tables following way:
> CREATE TEMPORARY table temp1
> Select link, max(dat) as m_dat, sum(value1) as val1
> from
> master
> left join child1 on master.link=child1.link1
> group by link
>
> CREATE TEMPORARY table temp2
> Select link, sum(value2) as val2
> from
> master
> left join child2 on master.link=child2.link2
> group by link
> //OK. Actually I use 2 steps to build one temporary
> table:
> a. Full CREATE TEMPORARY TABLE statement using KEY (or
> maybe better INDEX???) if needed.
> b. INSERT INTO to populate created table.
>
> FINALLY:
> Select m_dat, temp1.link, val1, val2
> from temp1, temp2
> where temp1.link=temp2.link
>
> Can I do it with one select statement in mysql?
>
> Sorry to say, I have to create up to 12 different
> temporary tables to get result sets for everyday use.
> As you may guess execution of bunch of queries takes
> time (up to 1 min) and may be considered as rather
> messy. Ive come to conclusion that SQL optimization
> with MySql (using temporary tables) is time consuming.
> IMHO I need feature called CREATE VIEW , but
> perhaps I have to learn some of features of standard
> SQL (i.e. progressive & extensive use of different
> JOIN types;-) so, really good SQL books regarding this
> subject; your recommendations.
>
> My database is not large (50 tables, max 25 columns
> per table, currently overall amount ~50Mb plus about
> 0.2-1Mb each day). I try to stick with SQL92 and keep
> my client code independent from particular SQL server
> implementation; maybe someday I have to shift to other
> back-end, so I want to make migration to different
> back-end as easy as possible. Speed penalty is
> inescapable.
>
> My system configuration is Compaq PROLIANT ML370,
> 128Mb RAM, 933 Mhz PIII, RedHat Linux 7.1. Its dual
> processor system, so some hardware upgrade is quite
> possible. I have small number of clients (1-20). Any
> suggestions for optimal MySql server configuration?
>
>
> Any help will be appreciated.
>
> p.s.
> Whats the difference between KEY and INDEX in CREATE
> TABLE syntax (any +/- effect on JOIN)?
>
> Regards,
> Alvis
>
>
> __
> Do You Yahoo!?
> Yahoo! Sports - Coverage of the 2002 Olympic Games
> http://sports.yahoo.com
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php (the manual)
>http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
