recursively isolate pedigrees, based only on person_id, mother_id and father

pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed

recursively isolate pedigrees, based only on person_id, mother_id and father_id
2+ messages / 2 participants
[nested] [flat]

* recursively isolate pedigrees, based only on person_id, mother_id and father_id
@ 2009-08-28 15:10  rawi <only4com@web.de>
  0 siblings, 1 reply; 2+ messages in thread

From: rawi @ 2009-08-28 15:10 UTC (permalink / raw)
  To: pgsql-sql


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-fath...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: recursively isolate pedigrees, based only on person_id, mother_id and father_id
@ 2009-08-28 16:19  Rob Sargent <robjsargent@gmail.com>
  parent: rawi <only4com@web.de>
  0 siblings, 0 replies; 2+ messages in thread

From: Rob Sargent @ 2009-08-28 16:19 UTC (permalink / raw)
  To: rawi <only4com@web.de>; +Cc: pgsql-sql

On the assumption that you wish to generate the pedigrees for analysis 
or charting,
why not perform the recursion in those layers (or their supporting 
software), This
does not require a large number of sql calls since using an in-clause 
each call
will gather one generation (ascending or descending) for all new persons 
retrieved
in any iteration. How deep are your pedigrees and what is their typical 
size?


rawi wrote:
> 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
>   



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2009-08-28 16:19 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-08-28 15:10 recursively isolate pedigrees, based only on person_id, mother_id and father_id rawi <only4com@web.de>
2009-08-28 16:19 ` Rob Sargent <robjsargent@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox