引用同一表的外键 [英] ForeignKey Referencing Same Table

问题描述

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

There was an interview test in which below was the table and structure

 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. "选择那些是约翰·史密斯"和简"的孩子的孩子

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

and I was puzzled because I was assuming that foreign key would be linked with some other table as usual. But at that point I failed. do some one know the actual answer and reason?

推荐答案

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

This kind of data structure is called a "Self Referencing Table"

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'

这篇关于引用同一表的外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！