· 6 years ago · Jun 30, 2019, 02:10 AM
1CREATE TABLE IF NOT EXISTS parent(
2 code varchar(3),
3 name varchar(10),
4 PRIMARY KEY(code)
5)ENGINE=INNODB;
6
7INSERT INTO parent(code,name) VALUES('001','Mary');
8INSERT INTO parent(code,name) VALUES('002','Joseph');
9INSERT INTO parent(code,name) VALUES('003','Adan');
10INSERT INTO parent(code,name) VALUES('004','Eva');
11INSERT INTO parent(code,name) VALUES('005','Ana');
12INSERT INTO parent(code,name) VALUES('006','Elcana');
13
14mysql> select * from parent;
15+------+--------+
16| code | name |
17+------+--------+
18| 001 | Mary |
19| 002 | Joseph |
20| 003 | Adan |
21| 004 | Eva |
22| 005 | Ana |
23| 006 | Elcana |
24+------+--------+
256 rows in set (0.01 sec)
26
27CREATE TABLE IF NOT EXISTS child(
28 code varchar(3),
29 name varchar(10),
30 PRIMARY KEY(code),
31 mother_code varchar(3),
32 father_code varchar(3),
33 FOREIGN KEY fk_mother_code(mother_code) REFERENCES parent(code),
34 FOREIGN KEY fk_father_code(father_code) REFERENCES parent(code)
35)ENGINE=INNODB;
36
37INSERT INTO child(code, name, mother_code, father_code) VALUES('001','Jesus', '001', '002');
38INSERT INTO child(code, name, mother_code, father_code) VALUES('002','Cain', '003', '004');
39INSERT INTO child(code, name, mother_code, father_code) VALUES('003','Abel', '003', '004');
40INSERT INTO child(code, name, mother_code, father_code) VALUES('004','Set', '003', '004');
41INSERT INTO child(code, name, mother_code, father_code) VALUES('005','Samuel', '005', '006');
42
43mysql> select * from child;
44+------+--------+-------------+-------------+
45| code | name | mother_code | father_code |
46+------+--------+-------------+-------------+
47| 001 | Jesus | 001 | 002 |
48| 002 | Cain | 003 | 004 |
49| 003 | Abel | 003 | 004 |
50| 004 | Set | 003 | 004 |
51| 005 | Samuel | 005 | 006 |
52+------+--------+-------------+-------------+
535 rows in set (0.00 sec)
54
55+------+--------+------+-------+-------------+-------------+
56| code | name | code | name | mother_code | father_code |
57+------+--------+------+-------+-------------+-------------+
58| 001 | Mary | 001 | Jesus | 001 | 002 |
59| 002 | Joseph | 001 | Jesus | 001 | 002 |
60+------+--------+------+-------+-------------+-------------+
61
62SELECT p.*, c.* FROM parent p,
63 child c,
64 (SELECT pm.code AS m_code FROM parent pm) AS m,
65 (SELECT pf.code AS f_code FROM parent pf) AS f
66 WHERE
67 m.m_code='001' AND
68 f.f_code='002' AND
69 c.mother_code=m.m_code AND
70 c.father_code=f.f_code AND
71 c.mother_code='001' AND
72 c.father_code='002' AND
73 c.code='001';
74
75+------+--------+------+-------+-------------+-------------+
76| code | name | code | name | mother_code | father_code |
77+------+--------+------+-------+-------------+-------------+
78| 001 | Mary | 001 | Jesus | 001 | 002 |
79| 002 | Joseph | 001 | Jesus | 001 | 002 |
80| 003 | Adan | 001 | Jesus | 001 | 002 |
81| 004 | Eva | 001 | Jesus | 001 | 002 |
82| 005 | Ana | 001 | Jesus | 001 | 002 |
83| 006 | Elcana | 001 | Jesus | 001 | 002 |
84+------+--------+------+-------+-------------+-------------+
856 rows in set (0.00 sec)