· 6 years ago · Jun 15, 2019, 10:16 PM
1-- --------------------------------------------------------- children
2
3CREATE TABLE IF NOT EXISTS children (
4 id INT AUTO_INCREMENT PRIMARY KEY,
5 child_name VARCHAR(50),
6 id_link INT(10)
7) ENGINE=INNODB;
8
9
10INSERT INTO children(child_name, id_link)
11VALUES
12 ('Liam', 1),
13 ('William', 2),
14 ('Benjamin', 3),
15 ('Elijah', 4),
16 ('Franklin', 5);
17
18
19
20 -- --------------------------------------------------------- child_diagnosis
21
22CREATE TABLE IF NOT EXISTS child_diagnosis (
23 id INT AUTO_INCREMENT PRIMARY KEY,
24 id_child INT(10),
25 id_disease INT(10)
26) ENGINE=INNODB;
27
28
29INSERT INTO child_diagnosis(id_child, id_disease)
30VALUES
31 (1,3),
32 (2,7),
33 (2,1),
34 (3,2),
35 (3,5),
36 (3,8),
37 (4,10);
38
39
40
41 -- --------------------------------------------------------- diseases
42
43
44CREATE TABLE IF NOT EXISTS diseases (
45 id INT AUTO_INCREMENT PRIMARY KEY,
46 class VARCHAR(50),
47 description VARCHAR(100)
48) ENGINE=INNODB;
49
50
51INSERT INTO diseases(class, description)
52VALUES
53 ('A00-B99', 'Certain infectious and parasitic diseases'),
54 ('C00-D48', 'Neoplasms'),
55 ('D50-D89', 'Diseases of the blood and blood-forming organs and certain disorders involving the immune mechanism'),
56 ('E00-E90', 'Endocrine, nutritional and metabolic diseases'),
57 ('F00-F99', 'Mental and behavioural disorders'),
58 ('G00-G99', 'Diseases of the nervous system'),
59 ('H00-H59', 'Diseases of the eye and adnexa'),
60 ('H60-H95', 'Diseases of the ear and mastoid process'),
61 ('I00-I99', 'Diseases of the circulatory system'),
62 ('K00-K93', 'Diseases of the digestive system');
63
64
65
66 -- --------------------------------------------------------- link
67
68
69CREATE TABLE IF NOT EXISTS link (
70 id INT AUTO_INCREMENT PRIMARY KEY,
71 id_mother INT(10)
72) ENGINE=INNODB;
73
74
75INSERT INTO link(id_mother)
76VALUES
77 (1),
78 (2),
79 (3),
80 (4),
81 (5);
82
83
84
85 -- --------------------------------------------------------- mothers
86
87
88CREATE TABLE IF NOT EXISTS mothers (
89 id INT AUTO_INCREMENT PRIMARY KEY,
90 mother_name VARCHAR(50)
91) ENGINE=INNODB;
92
93
94INSERT INTO mothers (mother_name)
95VALUES
96 ('Eva'),
97 ('Emma'),
98 ('Olivia'),
99 ('Mia'),
100 ('Sofia');
101
102
103
104 -- --------------------------------------------------------- mother_sickness
105
106
107CREATE TABLE IF NOT EXISTS mother_sickness (
108 id INT AUTO_INCREMENT PRIMARY KEY,
109 id_mother INT(10) NOT NULL,
110 id_disease INT(10) NOT NULL
111) ENGINE=INNODB;
112
113
114INSERT INTO mother_sickness (id_mother, id_disease)
115VALUES
116 (1,4),
117 (1,6),
118 (2,9),
119 (3,10),
120 (3,5),
121 (5,3);
122
123-- preparation
124SET sql_mode='';
125
126-- test
127select
128 mix.child_class as class,
129 mix.child_description as description,
130 count(mix.child_class) as overall_CHILDREN_disease_occurance,
131 count(mix.mother_class) as overall_MOTHER_disease_occurance,
132 count(case when mother_class = 'A00-B99' then mother_class else null end) as 'infections',
133 count(case when mother_class = 'C00-D48' then mother_class else null end) as 'neoplasms',
134 count(case when mother_class = 'D50-D89' then mother_class else null end) as 'blood diseases',
135 count(case when mother_class = 'F00-F99' then mother_class else null end) as 'mental disorders',
136 count(case when mother_class not in ('A00-B99', 'C00-D48', 'D50-D89', 'F00-F99') then mother_class else null end) as 'other diseases'
137from
138 (select
139 a.child_name,
140 a.child_class,
141 a.child_description,
142 b.mother_class,
143 b.moms_id
144from
145 (select
146 ch.child_name as child_name,
147 d1.class as child_class,
148 d1.description as child_description,
149 link.id_mother as moms_id
150 from children ch
151 join child_diagnosis ds on ds.id_child = ch.id
152 join diseases d1 on d1.id = ds.id_disease
153 join link on link.id = ch.id_link) a
154
155left join
156
157 (select
158 mo.mother_name as mother_name,
159 d2.class as mother_class,
160 d2.description as mother_description,
161 mo.id as moms_id
162 from mothers mo
163 join mother_sickness ms on ms.id_mother = mo.id
164 join diseases d2 on d2.id = ms.id_disease) b on b.moms_id = a.moms_id) mix
165group by mix.child_class
166with rollup;