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]

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.