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.
