5 messages in com.mysql.lists.perlRe: Perl Query Problem
FromSent OnAttachments
csl...@qxzi.net26 Apr 2004 20:10 
MTA-Traffic27 Apr 2004 10:03 
Rudy Lippan27 Apr 2004 11:43 
cp27 Apr 2004 13:41 
Rudy Lippan27 Apr 2004 14:19 
Subject:Re: Perl Query Problem
From:Rudy Lippan (rlip@remotelinux.com)
Date:04/27/2004 02:19:25 PM
List:com.mysql.lists.perl

On Tue, 27 Apr 2004, cp wrote:

On Apr 27, 2004, at 1:43 PM, Rudy Lippan wrote:

I have 10+ tables with some of the same information in each in table of a mysql database. Here is an example:

Any ideas?

Something like this:

SELECT name, sum(value) as total from (SELECT * from test1 UNION ALL SELECT * FROM test2) AS d_table group by name;

Cool. Which version of MySQL supports sub-selects?

4.1.x+

With version 4.0.13 the above code tosses an error. I had to create a temporary table and Insert Select the union into it.

That is Probably the best way in 4.0.

And the Perl code would then be something along the lines of : # untested for syntax errors. error checking omitted... #!/usr/bin/perl

use DBI;

my $dbh = DBI->connect('dbi:mysql:test;host=localhost', '', '', {RaiseError=>1}); $dbh->do( qq( Drop Table If Exists results ));

# assuming you don't have any other types of tables in this database my @list_of_tables = $dbh->tables();

$dbh->do( qq( Create Table results ( name varchar( 10 ), qty Int ) ) );

my $sql = 'Insert Into results ' . join(' Union All ', map{ "Select * from $_" } @list_of_tables ); $dbh->do ( $sql );

Or Combined into one statement: create temporary table t1 (SELECT * from test1)UNION ALL (SELECT * from test2);

my $results = $dbh->selectall_arrayref( qq(Select name, Sum(qty) from results Group By name)); print "| " . join("\t| ", @$_ ) . " | \n" for ( @$results );

Rudy