Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

i had a feeling that perl would be able to accomplish this. i.e. for level of 3, generate this output 
SELECT t1.name AS lev1,t2.name AS lev2,t3.name AS lev3
FROM tab AS t1
LEFT JOIN tab AS t2 ON t2.parentID = t1.ID
LEFT JOIN tab AS t3 ON t3.parentID = t2.ID
WHERE t1.parentID = 0

[download]
the purpose is to retrieve the whole tree from adjacent-list table with depth as an parameter.

Replies are listed 'Best First'.
Re: generate self join sql with specified depth level
by suaveant (Parson) on Dec 22, 2009 at 14:59 UTC
    I've done this at least a couple of times, it's not rocket science.

    Something along the lines of... 

    my $number_of_levels = 5;
my $query = 'T1.name AS lev1';
my $join = '';
my $old_table = 'T1';
for(2..$number_of_levels) {
  $query .= ", T$_.name AS lev$_";
  $join .= " LEFT JOIN tab AS T$_ ON T$_.parentID = $old_table.ID";
  $old_table = "T$_";
}

print $finished_query = "SELECT $query FROM tag AS T1 $join WHERE T1.p
+arentID = 0";

    [download]
    That should pretty much do it.

                    - Ant
                    - Some of my best work - (1 2 3)

[reply]
[d/l]

Back to Seekers of Perl Wisdom