Received: from blount.mail.mindspring.net (blount.mail.mindspring.net [207.69.200.226]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9TJicP54481 for ; Mon, 29 Oct 2001 14:44:38 -0500 (EST) (envelope-from greg@turnstep.com) Received: from dialup.mindspring.com (user-2inib15.dsl.mindspring.com [165.121.44.37]) by blount.mail.mindspring.net (8.9.3/8.8.5) with SMTP id OAA22290; Mon, 29 Oct 2001 14:44:35 -0500 (EST) Date: Mon, 29 Oct 2001 14:44:35 -0500 (EST) Message-Id: <200110291944.OAA22290@blount.mail.mindspring.net> To: From: "Greg Sabino Mullane" Subject: Re: Recursive select X-Echelon: Cocaine Treasury plutonium Saddam Hussein SDI X-PGP-Fingerprint: 2529 DF6A B8F7 9407 E944 45B4 BC9B 9067 1496 4AC8 X-Mailer: GregMail 1.21 X-Archive-Number: 200110/399 X-Sequence-Number: 5351 -----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@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-----