Как посчитать количество обьектов в суб-суб таблице?

Question

Есть вот такая структура

CREATE TABLE `rooms` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL COLLATE 'utf8_general_ci',
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `name` (`name`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=3
;

CREATE TABLE `students` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `ref_room_id` INT(10) NULL DEFAULT NULL,
    `student_name` VARCHAR(50) NOT NULL COLLATE 'utf8_general_ci',
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `FK_students_room` (`ref_room_id`) USING BTREE,
    CONSTRAINT `FK_students_room` FOREIGN KEY (`ref_room_id`) REFERENCES `university`.`rooms` (`id`) ON UPDATE CASCADE ON DELETE SET NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=6
;

CREATE TABLE `possessions` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `ref_student_id` INT(10) NOT NULL,
    `name` VARCHAR(50) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `FK__students` (`ref_student_id`) USING BTREE,
    CONSTRAINT `FK__students` FOREIGN KEY (`ref_student_id`) REFERENCES `university`.`students` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=4
;

Теперь добавляю сюда данные

INSERT INTO `university`.`rooms` (`name`) VALUES ('a');
INSERT INTO `university`.`rooms` (`name`) VALUES ('b');

INSERT INTO `university`.`students` (`ref_room_id`, `student_name`) VALUES ('3', '1');
INSERT INTO `university`.`students` (`ref_room_id`, `student_name`) VALUES ('3', '2');
INSERT INTO `university`.`students` (`ref_room_id`, `student_name`) VALUES ('3', '3');
INSERT INTO `university`.`students` (`ref_room_id`, `student_name`) VALUES ('3', '4');

INSERT INTO `university`.`possessions` (`ref_student_id`, `name`) VALUES ('7', 'a');
INSERT INTO `university`.`possessions` (`ref_student_id`, `name`) VALUES ('7', 'aa');
INSERT INTO `university`.`possessions` (`ref_student_id`, `name`) VALUES ('7', 'aaa');
INSERT INTO `university`.`possessions` (`ref_student_id`, `name`) VALUES ('8', 'aaaa');
INSERT INTO `university`.`possessions` (`ref_student_id`, `name`) VALUES ('9', 'aaaaa');
INSERT INTO `university`.`possessions` (`ref_student_id`, `name`) VALUES ('6', 'aaaaaa');
INSERT INTO `university`.`possessions` (`ref_student_id`, `name`) VALUES ('7', 'aaaaaaa');

Мне нужно получить таблицу в которой будет показано классы потом сколько студентов (количество) в каждом классе и сколько вещей у всех студентов в этом классе (пример внизу)

для этого я создал procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_data`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

SELECT r.id AS room_id, r.name, COUNT(s.id) AS student_num, COUNT(p.id) AS possessions_num
FROM rooms r 
INNER JOIN students s ON s.ref_room_id = r.id
INNER JOIN possessions p ON p.ref_student_id = s.id
GROUP BY r.name;

END

Но результат получается вот такой

Что делаю не так?

БЛОГ НА HUSL

Как посчитать количество обьектов в суб-суб таблице?

Ответы (0 шт):