ForeignKey Referencing Same Table

mysql, sql

Solution

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

and

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'

Problem

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 ``` and it was asked - "select all who are mothers" - "select those child who are children of 'John Smith' and 'Jane' 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?

Original source