Re: How to find Brothers and Sisters?

Neo wrote:
> Suppose Adam has children named John(male), Jack(male) and
> Mary(female). What schema/query finds John's brothers and sisters based
> only on direct relationships to parent and gender. A direct
> relationship would be parent/child. An indirect/derived relationship
> would be brother/sister. The query is not to use Adam directly. Below
> is a solution using dbd.
>
> (new 'male 'gender)
> (new 'female 'gender)
>
> (new 'adam 'person)
>
> (new 'john 'person)
> (set john gender male)
>
> (new 'jack 'person)
> (set jack gender male)
>
> (new 'mary 'person)
> (set mary gender female)
>
> (new 'child 'verb)
> (set adam child john)
> (set adam child jack)
> (set adam child mary)
>
> (; Get john's brothers
> by getting persons
> whose gender is male
> and is child of john's father
> and that person is not himself)
> (; Gets jack)
> (!= (and (get person instance *)
> (get * gender male)
> (get (get * child john) child *))
> john)
>
> (; Get john's sisters
> by getting persons
> whose gender is female
> and is child of john's father
> and that person is not himself)
> (; Gets mary)
> (!= (and (get person instance *)
> (get * gender female)
> (get (get * child john) child *))
> john)

In addition to Cimode's answer, here is a slight variation:

[ltjn_at_lelles ~/src/parents]$ cat parent.sql

drop table persons;
create table persons (

        name char(10) not null primary key,
        father char(10) references persons on delete restrict,
        gender char(1) not null check (gender in ('M','F'))

insert into persons (name, gender) values ('Adam', 'M');

insert into persons (name, father, gender)

        values  ('Mary','Adam','F'),
                ('John','Adam','M'),
                ('Jack','Adam','M');

[ltjn_at_lelles ~/src/parents]$ db2 "select * from persons p where p.father = (select father from persons where name = 'John') and name <> 'John' and gender = 'F'"

NAME FATHER GENDER
---------- ---------- ------
Mary Adam F

  1 record(s) selected.

[ltjn_at_lelles ~/src/parents]$ db2 "select * from persons p where p.father = (select father from persons where name = 'John') and name <> 'John' and gender = 'M'"

NAME FATHER GENDER
---------- ---------- ------
Jack Adam M

  1 record(s) selected.

/Lennart Received on Sun Dec 03 2006 - 21:48:36 CET