Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 73E0B6354BE for ; Fri, 28 Aug 2009 12:10:29 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 91034-10 for ; Fri, 28 Aug 2009 15:10:21 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from kuber.nabble.com (kuber.nabble.com [216.139.236.158]) by mail.postgresql.org (Postfix) with ESMTP id 52550634ED4 for ; Fri, 28 Aug 2009 12:10:21 -0300 (ADT) Received: from isper.nabble.com ([192.168.236.156]) by kuber.nabble.com with esmtp (Exim 4.63) (envelope-from ) id 1Mh35o-00045T-1j for pgsql-sql@postgresql.org; Fri, 28 Aug 2009 08:10:20 -0700 Message-ID: <25191664.post@talk.nabble.com> Date: Fri, 28 Aug 2009 08:10:20 -0700 (PDT) From: rawi To: pgsql-sql@postgresql.org Subject: recursively isolate pedigrees, based only on person_id, mother_id and father_id MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Nabble-From: only4com@web.de X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 200908/148 X-Sequence-Number: 33057 Hello! Given a table "persons", where the individuals are bound to each other only via the foreign keys mother_id and father_id as self joins on persons, like that create table persons ( id serial primary key, sex character(1), father_id integer default null references persons(id) on update cascade on delete set null, mother_id integer default null references persons(id) on update cascade on delete set null ); INSERT INTO persons(id, sex, father_id,mother_id) VALUES(1,'m',null,null); INSERT INTO persons(id, sex, father_id,mother_id) VALUES(2,'f',null,null); INSERT INTO persons(id, sex, father_id,mother_id) VALUES(8,'m',null,null); INSERT INTO persons(id, sex, father_id,mother_id) VALUES(9,'f',null,null); INSERT INTO persons(id, sex, father_id,mother_id) VALUES(3,'m',1,2); INSERT INTO persons(id, sex, father_id,mother_id) VALUES(4,'f',8,9); INSERT INTO persons(id, sex, father_id,mother_id) VALUES(5,'f',3,4); INSERT INTO persons(id, sex, father_id,mother_id) VALUES(6,'m',3,4); INSERT INTO persons(id, sex, father_id,mother_id) VALUES(7,'m',8,9); INSERT INTO persons(id, sex, father_id,mother_id) VALUES(10,'m',null,null); INSERT INTO persons(id, sex, father_id,mother_id) VALUES(11,'m',10,5); INSERT INTO persons(id, sex, father_id,mother_id) VALUES(12,'f',7,5); -- consanguinity INSERT INTO persons(id, sex, father_id,mother_id) VALUES(100,'m',null,null); INSERT INTO persons(id, sex, father_id,mother_id) VALUES(200,'f',null,null); INSERT INTO persons(id, sex, father_id,mother_id) VALUES(300,'m',100,200); INSERT INTO persons(id, sex, father_id,mother_id) VALUES(400,'f',null,null); INSERT INTO persons(id, sex, father_id,mother_id) VALUES(500,'f',300,400); These would be the graphs of the 2 families: http://www.nabble.com/file/p25191664/family1.jpg http://www.nabble.com/file/p25191664/family2.jpg I hoped to find a recursive SQL or function, which would extract the WHOLE family of any given person.id from the table with many families. After failing to accomplish this with a recursive SQL I found on the web an advice from Celko (???) http://www.eggheadcafe.com/conversation.aspx?messageid=29498840&threadid=29498808 to better keep away form such things... :( Did someone gathered a closer expertise to that? Thank you very much for any hint! Regards Rawi -- View this message in context: http://www.nabble.com/recursively-isolate-pedigrees%2C-based-only-on-person_id%2C-mother_id-and-father_id-tp25191664p25191664.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.