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
parentIDFROM 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@turnstep.com
PGP Key: 0x14964AC8 200110291441

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

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