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
