yqngbF50

1#схема
2
3CREATE TABLE IF NOT EXISTS `People` (
4  `id` int(6) unsigned NOT NULL,
5  `somevalue` int(3) unsigned NOT NULL,
6  `name` varchar(200) NOT NULL,
7  PRIMARY KEY (`id`)
8) DEFAULT CHARSET=utf8;
9
10CREATE TABLE IF NOT EXISTS `Family` (
11  `id` int(6) unsigned NOT NULL,
12  `id_people` int(3) unsigned NOT NULL,
13  `age` int(3) unsigned NOT NULL,
14  `name` varchar(200) NOT NULL,
15  PRIMARY KEY (`id`)
16) DEFAULT CHARSET=utf8;
17
18INSERT INTO `Family` (`id`, `id_people`, `age`, `name`) VALUES
19  ('1', '1', '15', 'Test1'),
20  ('2', '1', '16', 'Test1'),
21  ('3', '1', '17', 'Test1'),
22  ('4', '2', '18', 'Test1'),
23  ('5', '2', '17', 'Test1'),
24  ('6', '3', '15', 'Test1'),
25  ('7', '3', '19', 'Test1');
26  
27  
28  INSERT INTO `People` (`id`, `somevalue`, `name`) VALUES
29  ('1', '1', 'Kapeh'),
30  ('2', '1', 'Redy'),
31  ('3', '1', 'Ftpud');
32
33
34
35# начальный реквест
36select People.* from People
37  where (select count(Family.id) from Family where Family.id_people = People.id) >= 2
38   AND 
39   EXISTS (select Family.id from Family where Family.id_people = People.id AND Family.age < 18);
40   
41# вариант с иннер джойном   
42select * from (
43    select 
44    People.id, People.somevalue, People.name, count(*) as cnt, min(Family.age) as age from People 
45    INNER JOIN Family on 
46    Family.id_people=People.id group by People.name) as T
47  where T.age < 18 and cnt >= 2;
48 
49 # вариант с реквестом в реквесте
50 select (select People.name from People where People.id=T.id_people) from ( 
51  select *,min(age) as m_age,count(*) as cnt from Family group by id_people) as T
52  where m_age < 18 and cnt >= 2;