引用同一表的外键

Question

进行了一次面试，下面是表格和结构

 Table Person = id, name, dob, dod, mother_id, father_id
 Primary Key (id)
 Foreign Key mother_id references Person
 Foreign Key father_id references Person

有人问

1. “选择所有母亲”
2. “选择那些是“约翰·史密斯”和“简”的孩子
   
   

我很困惑，因为我假设外键会像往常一样与其他表链接。但是那时我失败了。有人知道实际答案和原因吗？

Answer 1

这种数据结构称为“自引用表”

SELECT DISTINCT mothers.*
FROM person
    inner join person mothers on person.mother_id = mothers.id

和

SELECT person.*
FROM person
    inner join person fathers on person.father_id = fathers.id
    inner join person mothers on person.mother_id = mothers.id
WHERE 
    fathers.name='john smith'
and 
    mothers.name='jane'