Оператор выбора SQL: один ребенок - два родителя (два разных FK)
У меня есть ситуация, когда лучше всего объяснить это через следующий сценарий:
- где запись
Childимеет двеParent(мать и отец)
Если вы потом прочитаете этот пост, то рассмотрите вариант применения сценария self-referencing tables, не обязательно, нет ни дедов, ни внуков.
О родитель определяется как:
CREATE TABLE IF NOT EXISTS parent(
code varchar(3),
name varchar(10),
PRIMARY KEY(code)
)ENGINE=INNODB;
Записи вставляются как:
INSERT INTO parent(code,name) VALUES('001','Mary');
INSERT INTO parent(code,name) VALUES('002','Joseph');
INSERT INTO parent(code,name) VALUES('003','Adan');
INSERT INTO parent(code,name) VALUES('004','Eva');
INSERT INTO parent(code,name) VALUES('005','Ana');
INSERT INTO parent(code,name) VALUES('006','Elcana');
И запрос select работает так, как ожидается:
mysql> select * from parent;
+------+--------+
| code | name |
+------+--------+
| 001 | Mary |
| 002 | Joseph |
| 003 | Adan |
| 004 | Eva |
| 005 | Ana |
| 006 | Elcana |
+------+--------+
6 rows in set (0.01 sec)
О ребенок определяется как:
CREATE TABLE IF NOT EXISTS child(
code varchar(3),
name varchar(10),
PRIMARY KEY(code),
mother_code varchar(3),
father_code varchar(3),
FOREIGN KEY fk_mother_code(mother_code) REFERENCES parent(code),
FOREIGN KEY fk_father_code(father_code) REFERENCES parent(code)
)ENGINE=INNODB;
Наблюдение: сверху наблюдайте, как Child ожидает дваPKs от Parent (предположим, что они должны быть разными) через два FKs.
Записи вставляются как:
INSERT INTO child(code, name, mother_code, father_code) VALUES('001','Jesus', '001', '002');
INSERT INTO child(code, name, mother_code, father_code) VALUES('002','Cain', '003', '004');
INSERT INTO child(code, name, mother_code, father_code) VALUES('003','Abel', '003', '004');
INSERT INTO child(code, name, mother_code, father_code) VALUES('004','Set', '003', '004');
INSERT INTO child(code, name, mother_code, father_code) VALUES('005','Samuel', '005', '006');
И запрос select работает так, как ожидается:
mysql> select * from child;
+------+--------+-------------+-------------+
| code | name | mother_code | father_code |
+------+--------+-------------+-------------+
| 001 | Jesus | 001 | 002 |
| 002 | Cain | 003 | 004 |
| 003 | Abel | 003 | 004 |
| 004 | Set | 003 | 004 |
| 005 | Samuel | 005 | 006 |
+------+--------+-------------+-------------+
5 rows in set (0.00 sec)
Цель получить следующее:
+------+--------+------+-------+-------------+-------------+
| code | name | code | name | mother_code | father_code |
+------+--------+------+-------+-------------+-------------+
| 001 | Mary | 001 | Jesus | 001 | 002 |
| 002 | Joseph | 001 | Jesus | 001 | 002 |
+------+--------+------+-------+-------------+-------------+
Я пробовал следующее:
SELECT p.*, c.* FROM parent p,
child c,
(SELECT pm.code AS m_code FROM parent pm) AS m,
(SELECT pf.code AS f_code FROM parent pf) AS f
WHERE
m.m_code='001' AND
f.f_code='002' AND
c.mother_code=m.m_code AND
c.father_code=f.f_code AND
c.mother_code='001' AND
c.father_code='002' AND
c.code='001';
Предложение where выглядело бы избыточным, потому что я пытаюсь получить желаемый результат, поэтому оно содержит попытки написать правильный запрос.
Но всегда возвращает:
+------+--------+------+-------+-------------+-------------+
| code | name | code | name | mother_code | father_code |
+------+--------+------+-------+-------------+-------------+
| 001 | Mary | 001 | Jesus | 001 | 002 |
| 002 | Joseph | 001 | Jesus | 001 | 002 |
| 003 | Adan | 001 | Jesus | 001 | 002 |
| 004 | Eva | 001 | Jesus | 001 | 002 |
| 005 | Ana | 001 | Jesus | 001 | 002 |
| 006 | Elcana | 001 | Jesus | 001 | 002 |
+------+--------+------+-------+-------------+-------------+
6 rows in set (0.00 sec)
Итак, каково правильное предложение?
Научитесь правильно использовать JOIN. Он существует уже более 25 лет.
Нет верха, только один ребенок и двое родителей
Ответы 3
Вы просто ищете два joins?
select c.*, pm.name as mother_name, pf.name as father_name
from child c join
parent pm
on c.mother_code = pm.code join
parent pf
on c.father_code = pf.code;
Вы можете добавить предложение where, чтобы отфильтровать это до конкретных детей:
where c.code in ('001', '002')
Кажется, путь здесь, но ваш запрос возвращает 5 записей, я добавлю пункт where
Из вашего ожидаемого результата я думаю, что это то, что вам нужно:
select
p.code, p.name,
c.code, c.name,
c.mother_code, c.father_code
from parent p
inner join child c
on c.mother_code = p.code or c.father_code = p.code
Вы можете добавить любое необходимое условие с помощью предложения WHERE.
Смотрите демо.
Результаты:
| code | name | code | name | mother_code | father_code |
| ---- | ------ | ---- | ------ | ----------- | ----------- |
| 001 | Mary | 001 | Jesus | 001 | 002 |
| 002 | Joseph | 001 | Jesus | 001 | 002 |
| 003 | Adan | 002 | Cain | 003 | 004 |
| 004 | Eva | 002 | Cain | 003 | 004 |
| 003 | Adan | 003 | Abel | 003 | 004 |
| 004 | Eva | 003 | Abel | 003 | 004 |
| 003 | Adan | 004 | Set | 003 | 004 |
| 004 | Eva | 004 | Set | 003 | 004 |
| 005 | Ana | 005 | Samuel | 005 | 006 |
| 006 | Elcana | 005 | Samuel | 005 | 006 |
Я получил тот же результат, мне нужно добавить предложение where, потому что мне нужны только две первые записи
Насколько я понимаю, условие должно быть примерно таким: WHERE c.name = 'Jesus'
Да, ваш вывод позволяет увидеть формат, который я запросил, показывая две строки, представляющие мать и отца.
Если зависимость от дочернего элемента заключается в том, чтобы получить родителей, используйте или на вашем соединении.
SELECT p.Code, p.[Name], c.Code, p.[Name], c.Mother_Code, c.Father_Code
FROM Parent p JOIN Child c ON c.Mother_Code = p.Code OR c.Father_Code = p.Code
WHERE c.name = 'Jesus'
Если зависимость заключается в поиске дочерних элементов родителя, просто измените оператор WHERE
SELECT p.Code, p.[Name], c.Code, p.[Name], c.Mother_Code, c.Father_Code
FROM Parent p JOIN Child c ON c.Mother_Code = p.Code OR c.Father_Code = p.Code
WHERE p.name IN ('Mary', 'Joseph')
Вы просто пытаетесь показать лучшие 2 результата? Что делает ваш результат не желаемым?