Re: Recursive select

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> Is there any simple select statement that can get all rows
> with MyID = 5 and all its parents?

Simple? No. You can do it directly with SQL if you know how
"deep" you want to recurse. For example, a single level
would be:

SELECT * FROM foo
WHERE myid = 5
OR myid IN
(SELECT parentid FROM foo WHERE myID=5)

while two levels would be:

SELECT * FROM foo
WHERE myid = 5
OR myid IN
(SELECT parentid FROM foo WHERE myID=5)
OR myID in
(SELECT parentID FROM foo WHERE myid in
(SELECT parentID FROM foo WHERE myid=5));

etc. It only gets worse from there. :) For unlimited recursion,
you'll have to have a client slurp all the parent and children
into some sort of lookup table (e.g. a hash) and then loop
through until you've found them all. Here's a small perl example,
assuming you are using DBI, RaiseError is true, and 'myid'
is a unique column:

my $FOO_SQL = "SELECT myid, parentid, 'whateverelse' FROM foo";
my $foo_h = $dbh->prepare($FOO_SQL);
$foo_h->execute();
my %parent;
while(defined($_=$foo_h->fetchrow_arrayref())) {
$parent{$_->[0]}=$_->[1];
}

my %seenit;
my $winner = 5;
{
my $newwinner = $parent{$winner};
last if !defined $newwinner or $seenit{$newwinner}++;
print "Found myid: $winner parentid: $newwinner\n";
$winner = $newwinner;
redo;
}

Doesn't have to perl of course, but it has to be something that
can store variables and perform basic iteration.

Greg Sabino Mullane
greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200110291441

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iQA/AwUBO92xbLybkGcUlkrIEQL4PACgx0IvEybczRwcepxjYwWOhEEEwx0AnRNW
dYx/8hHp36sjWhpXyv+cN3sc
=NvbM
-----END PGP SIGNATURE-----