ForeignKey, ссылающийся на ту же таблицу

Был проведен опрос, в котором ниже была таблица и структура

 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

Такая структура данных называется "Самой ссылочной таблицей"

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'

Ответ 2

У вас всегда может быть внешний ключ, который ссылается на одну и ту же таблицу. В этом нет проблем.

Смотрите, предположите, что вы храните запись человека в таблице, теперь у этого человека будут мать и отец. И mother и father оба сами являются людьми.. поэтому они сами являются записью одной и той же таблицы.

Предположим, что у вас есть две записи в таблице Person: -

id     name           age         mother_id
1      xyz            24          5
5      abc            57          6

Итак, из приведенной выше таблицы вы видите, что person с id = 5 на самом деле является матерью человека с id = 1.. Таким образом, это foreign key ссылка на ту же таблицу.

Итак, вместо выполнения операции join с другой таблицей вам необходимо выполнить соединение с той же таблицей.

SELECT p2.id FROM
Person p1 join Person p2
WHERE p1.mother_id = p2.id

Этот запрос выберет запись mother вашей текущей записи.

Ответ 3

Я предполагал, что внешний ключ будет связан с какой-либо другой таблицей как обычно.

Он по-прежнему - он ссылается на другие записи в одной таблице Person.

-- All Mothers
SELECT mom.name
   FROM Person mom
   WHERE EXISTS (SELECT 1 FROM Person WHERE mother_id = mom.id);

-- Children of John Smith and Jane
SELECT kid.name
   FROM Person kid
   INNER JOIN Person mom on kid.mother_id = mom.id
   INNER JOIN Person dad on kid.father_id = dad.id
   WHERE mom.name = 'Jane' AND
   dad.name = 'John Smith';

Посмотрите на скрипт SQL здесь

Ответ 4

SELECT * 
FROM Person 
WHERE father_id = (SELECT id FROM Person WHERE name = 'John Smith')
AND    mother_id = (SELECT id FROM Person WHERE name = 'Jane')

Ответ 5

Ответ уже был дан podiluska, просто объясняя, как это работает, поскольку похоже, что вы новичок в MySql.

Предоставляя псевдоним таблице (как мать или отец для человека таблицы), вы делаете что-то вроде псевдо-таблицы, которую MySql интерпретирует как другую таблицу, поэтому соединение происходит нормально, просто представьте, что теперь есть 3 таблицы, Человек, Отец и Мать, и все они связаны между собой соединением.