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)

<<<<What schema/query finds John's brothers and sisters based
only on direct relationships to parent and gender>>>>

Something like this should do...


SK: Suroggate key
NN: Non NULL

name+sex uniquely identifies person

nodes: id(SK), name, sex

1(SK), Adam, Male
2(SK), John, Male
3(SK), Jack, Male
4(SK), Mary, Female

linkarent(FK), child(FK)
parent, child
1, 2
1, 3
1, 4


Getting John's brother and sisters without involving Adam in da query
-------------------------------------------------------------------------
select P2.name, P2.sex from person P2
inner join link
on P2.id = link.parent
inner join (select id from link inner join person P1 on child =
person.id where person.name = 'Jonh') B
on P2.id = B.id
where P2.name <> 'John'

)

Thx. I am having trouble determining if our solutions in RMDB/SQL are
equivalent. I have verified the following in MS Access. Are they
equivalent? (I realize above query is for brothers and sisters where as
the following is just for brothers)

SELECT P2.name
FROM ((person INNER JOIN link ON person.ID = link.childID)
INNER JOIN link AS link2 ON link.parentID = link2.parentID)
INNER JOIN person AS P2 ON link2.childID = P2.ID
WHERE (((P2.name)<>"John")
AND ((person.name)="John")
AND ((P2.sex)="Male"));

Just for sisters...

select P2.name, P2.sex
from person P2 inner join link on P2.id = link.parent
inner join (select id from link inner join person P1
on child = person.id where person.name = 'John') B
on P2.id = B.id
where P2.name <> 'John'
and P2.sex='Female'

When I try to run the above query in Access, I get the following
message: "Syntax error (missing operator) in query expression P2.id =
link.parent inner join .... P2.id = B.id"

What am I doing wrong? Below is the query for john's sisters that
executes ok.

SELECT P2.name
FROM ((person INNER JOIN link ON person.id = link.child)
INNER JOIN link AS link2 ON link.parent = link2.parent)
INNER JOIN person AS P2 ON link2.child = P2.id
WHERE (((P2.name)<>"John")
AND ((person.name)="John")
AND ((P2.sex)="Female"));


person
id name sex
1 Adam Male
2 John Male
3 Jack Male
4 Mary Female


link
parent child
Adam John
Adam Jack
Adam Mary

Neo a écrit :

do not think Access does JOIN on selects. You may need to package the
inner select in a view then do a JOIN on that view...

I packaged the inner expression in query Q_B as follows:
SELECT person.id
FROM link INNER JOIN person ON link.child = person.id
WHERE (((person.name)='John'));

Updated original query as follows:
select P2.name
from person P2 inner join link on P2.id = link.parent
inner join Q_B B on P2.id = B.id
where P2.name <> 'John'
and P2.sex='Female'

However I still get the previous missing operator error. The SQL Server
query may not be appropriate. At the very least, on the second line of
query, "P2.id = link.parent" should be "P2.id = link.child" because
mary joins to link table via link.child not link.parent. In addition,
the inner select should probably get parent's id from link table and
not john's id from person table. How does this query ensure that
siblings share a common parent? Could someone explain.

Neo a écrit :


Please repackage
<<
create view as Q_B
SELECT DISTINCT person.id
FROM link INNER JOIN person ON link.child = person.id
WHERE (((person.name)='John'))>>

then

select P2.name from person P2
inner join link on Q_B B on P2.parent = B.id
where P2.name <> 'John'

Should be sufficient to get all childs of John's parent... (If I am not
mistaken..)

Explanations...

restricts name or some operator..Cumulated view should be...

Hope this helps...

To get John's parent's id, shouldn't the query select link.parent
instead of person.id?

P2 is an alias for person table, so P2.parent is really person.parent,
but the person table doesn't have a parent field (link table does)?
Also is joining person and link tables based on Q_B valid?