-----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-----
