When you have a problem like "I need to create a string made up from values stored within arrays" you should think "maybe join will work."

When you have a problem like "has to work for any amount of arrays or values" you should think "maybe map or foreach will work."

First, you need to reduce the problem down to simpler sub-problems. Reformat your desired results so that similar structures are grouped together. Also split up separators and iteration patterns.

view0

 full outer join

view1 on
 view0.size = view1.size
 and
 view0.buffersize = view1.buffersize

 full outer join

view2 on
 view1.size = view2.size
 and
 view1.buffersize = view2.buffersize

full outer join

view3 on
 view2.size = view3.size
 and
 view2.buffersize = view3.buffersize
[download]

Now it's a bit easier to see the sub-problems. I've also marked each sub-problem as a join, map, or foreach kind of problem. It doesn't really matter if you get this wrong. As you try to solve each sub-problem the right solution will emerge. Here are the sub-problems I see:

1. create the "view0" statement -- join + map
2. create the "view? on" clauses
   1. match key columns between tables -- map
   2. generate a SQL comparison -- join
   3. combine with "and" -- join
3. run the query (DBI)
4. fetch results for an unknown number of columns (DBI)

The problems marked (DBI) can be handled by DBI directly -- RTFM. I'm not going to step through the process of solving each sub-problem, I'm just going to dump out my first cut at the solutions:

use strict

my $last = 'view0';

print join(' full outer join ', 'view0', view_on_clauses()),"\n";

sub view_on_clauses {
    return map { join(' ', $_, 'on', and_clause($_)) }
               qw(view1 view2 view3);
}

sub and_clause {
    my($view) = @_;
    return join(' and ', equality_clauses($view));
}

sub equality_clauses {
    my($view) = @_;

    my @equality_clauses = map { "$last.$_=$view.$_" }
                               qw(size buffersize);
    $last = $view;
    return @equality_clauses;
}
[download]

Right away we see a problem with your input and the first solution cut: hashes are unordered, but the solution requires an array of view names in a specific order. Also, the column names need to be an array, but you have them stored as strings. One thing that the first cut doesn't do is handle different column names for each view. Do you want to do that or should all columns names be the same?

Here's my final solution after fixing up those two issues and then replacing the hard-coded arrays with variables:

use strict;

my @views = ( 'view0', 'view1', 'view2', 'view3' );

my %columns = ( 'view0' => [qw(buffersize size value)],
                'view1' => [qw(buffersize size value)],
                'view2' => [qw(buffersize size value)],
                'view3' => [qw(buffersize size value)] );

print build_query(\@views, \%columns), "\n";

my $last;

sub build_query {
    my($views, $columns) = @_;

    my $first = shift @{$views};
    $last = $first;
    return join(' full outer join ', $first,
                view_on_clauses($views, $columns));
}

sub view_on_clauses {
    my($views, $columns) = @_;
    return map { join(' ', $_, 'on', and_clause($_, $columns)) } @{$vi
+ews};
}

sub and_clause {
    my($view, $columns) = @_;
    return join(' and ', equality_clauses($view, $columns));
}

sub equality_clauses {
    my($view, $columns) = @_;

    my @equality_clauses = ( );

    # Convert map into explicit for loop because we need to
    # merge together two different arrays. If the column names
    # are always the same, then map can be used.

    for (my $i = 0; $i < @{$columns->{$view}} - 1; ++$i) {
        push @equality_clauses,
             $last . '.' . $columns->{$last}[$i] . '=' .
             $view . '.' . $columns->{$view}[$i];
    }

    $last = $view;
    return @equality_clauses;
}
[download]

%hash = ( "view0" => [qw/buffersize size value/],
      "view1" => [qw/buffersize size value/],
      "view2" => [qw/buffersize size value/],
      "view3" => [qw/buffersize size value/]);

my $lastview;
my $output;
foreach $key (sort(keys %hash)){
    # Do this only the first time.
    if(not defined($lastview)){
    $lastview = $key;
    $output = "$key";
    next;
    }

    $output .= " full outer join $key on $lastview.$hash{$lastview}[1]
+ = $key.$hash{$key}[1] and $lastview.$hash{$lastview}[0] = $key.$hash
+{$lastview}[0]";
    $lastview = $key;
}

print $output, "\n";
[download]

Brilliant answer RollyGuy and it works pefectly, almost.
I wish I was proficiant enough in Perl to correct it myself but alas, not yet.
The problem is this like I said in between the code snippets in each statement the last item fom the arrays are missing. However in the code above it is presumed that it is only the first two items that are selected each time, when in fact only the last needs to be omitted.

input:
%hash = ( "view0" => "buffersize, size, value, speed",
          "view1" => "buffersize, size, value, speed",
          "view2" => "buffersize, size, value, speed"
          "view3" => "buffersize, size, value, speed")

output:
view0 full outer join view1 on view0.size = view1.size and view0.buffe
+rsize = view1.buffersize and view0.value = view1.value full outer joi
+n view2 on view1.size = view2.size and view1.buffersize = view2.buffe
+rsize and view1.value = view2.value full outer join view3 on view2.si
+ze = view3.size and view2.buffersize = view3.buffersize and view2.val
+ue = view3.value
[download]

j o h n i r l .

Sum day soon I'Il lern how 2 spelI (nad tYpe)

I see where I missed part of your specification. Here is try number 2. Now, the thing to note here is that my output is slightly different than the one you posted above. The "whatever = something" pairs are moved around a little bit. If I remember my SQL syntax, I don't believe it should matter too much. Let me know if it does, and I can help you fix it. Anyhow, here is the new updated code:



%hash = ( "view0" => [qw/buffersize size value speed/],
      "view1" => [qw/buffersize size value speed/],
      "view2" => [qw/buffersize size value speed/],
      "view3" => [qw/buffersize size value speed/]);

my $lastview;
my $output;
foreach $key (sort(keys %hash)){
    # Do this only the first time.
    if(not defined($lastview)){
    $lastview = $key;
    $output = "$key";
    next;
    }
    
    $output .= " full outer join $key on ";
    my $i;
    $array_elements_used = scalar(@{ $hash{$key} })-1;
    #loop through all of the elements except for the last
    for($i=0;$i<$array_elements_used;$i++){
    $output .= "$lastview.$hash{$lastview}[$i] = $key.$hash{$key}[$i]"
+;
    # only add an "and" if this is not the last element used.
    $output .= " and " if ($i != ($array_elements_used-1));
    }
    $lastview = $key;
}

print $output, "\n";

=============
output:
view0 full outer join view1 on view0.buffersize = view1.buffersize and
+ view0.size = view1.size and view0.value = view1.value full outer joi
+n view2 on view1.buffersize = view2.buffersize and view1.size = view2
+.size and view1.value = view2.value full outer join view3 on view2.bu
+ffersize = view3.buffersize and view2.size = view3.size and view2.val
+ue = view3.value
[download]