MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `shops` ( `guid` VARCHAR(36) NOT NULL,
                       `name` VARCHAR(200) NOT NULL );
INSERT INTO `shops` (`guid`, `name`) VALUES 
('9d71fd05-c9a7-11e0-8a85-00e081d27a45', 'Магаз1');SELECT * FROM shops;


guid
name


9d71fd05-c9a7-11e0-8a85-00e081d27a45
Магаз1


CREATE TABLE `nomenclature` ( `guid` VARCHAR(36) NOT NULL,
                              `name` VARCHAR(900) NULL DEFAULT NULL );
INSERT INTO `nomenclature` (`guid`, `name`) VALUES 
('9d7e815d-4439-11ea-80e3-0cc47aace875', 'Номен1'),
('916a367c-4439-11ea-80e3-0cc47aace875', 'Номен2'),
('916a3679-4439-11ea-80e3-0cc47aace875', 'Номен3');
SELECT * FROM nomenclature;


guid
name


9d7e815d-4439-11ea-80e3-0cc47aace875
Номен1

916a367c-4439-11ea-80e3-0cc47aace875
Номен2

916a3679-4439-11ea-80e3-0cc47aace875
Номен3


CREATE TABLE `rack` ( `guid` VARCHAR(36) NOT NULL,
                      `guid_shop` VARCHAR(36) NOT NULL,
                      `name` VARCHAR(200) NOT NULL );
INSERT INTO `rack` (`guid`, `guid_shop`, `name`) VALUES 
('63a6c768-532f-11e9-80df-0cc47aace875', '9d71fd05-c9a7-11e0-8a85-00e081d27a45', 'шкаф1');
SELECT * FROM rack;


guid
guid_shop
name


63a6c768-532f-11e9-80df-0cc47aace875
9d71fd05-c9a7-11e0-8a85-00e081d27a45
шкаф1


CREATE TABLE `shelf` ( `guid_shelf` VARCHAR(36) NOT NULL,
                       `guid_nomenclature` VARCHAR(36) NOT NULL );
INSERT INTO `shelf` (`guid_shelf`, `guid_nomenclature`) VALUES 
('806deed0-532f-11e9-80df-0cc47aace875', '9d7e815d-4439-11ea-80e3-0cc47aace875'),
('806deed0-532f-11e9-80df-0cc47aace875', '916a367c-4439-11ea-80e3-0cc47aace875'),
('806deed0-532f-11e9-80df-0cc47aace875', '916a3679-4439-11ea-80e3-0cc47aace875');
SELECT * FROM shelf;


guid_shelf
guid_nomenclature


806deed0-532f-11e9-80df-0cc47aace875
9d7e815d-4439-11ea-80e3-0cc47aace875

806deed0-532f-11e9-80df-0cc47aace875
916a367c-4439-11ea-80e3-0cc47aace875

806deed0-532f-11e9-80df-0cc47aace875
916a3679-4439-11ea-80e3-0cc47aace875


CREATE TABLE `shelf_reference` ( `guid` VARCHAR(36) NOT NULL,
                                 `guid_rack` VARCHAR(36) NOT NULL );
INSERT INTO `shelf_reference` (`guid`, `guid_rack`) VALUES 
('806deed0-532f-11e9-80df-0cc47aace875', '63a6c768-532f-11e9-80df-0cc47aace875');
SELECT * FROM shelf_reference;


guid
guid_rack


806deed0-532f-11e9-80df-0cc47aace875
63a6c768-532f-11e9-80df-0cc47aace875


SELECT t1.name, t2.name, t5.name
FROM shops t1
JOIN rack t2 ON t1.guid = t2.guid_shop
JOIN shelf_reference t3 ON t2.guid = t3.guid_rack
JOIN shelf t4 ON t3.guid = t4.guid_shelf
JOIN nomenclature t5 ON t4.guid_nomenclature = t5.guid;


name
name
name


Магаз1
шкаф1
Номен1

Магаз1
шкаф1
Номен2

Магаз1
шкаф1
Номен3


CREATE TABLE `rack_from_arduino` (
	`guid_nomeclature` VARCHAR(36) NOT NULL,
	`rack_index` VARCHAR(36) NOT NULL
)
;
INSERT INTO `rack_from_arduino` (`guid_nomeclature`, `rack_index`) VALUES ('9d7e815d-4439-11ea-80e3-0cc47aace875', '1.1.1');
INSERT INTO `rack_from_arduino` (`guid_nomeclature`, `rack_index`) VALUES ('916a367c-4439-11ea-80e3-0cc47aace875', '1.1.1');
INSERT INTO `rack_from_arduino` (`guid_nomeclature`, `rack_index`) VALUES ('916a3679-4439-11ea-80e3-0cc47aace875', '1.1.1');
SELECT * FROM rack_from_arduino;
////1(rack).1(shelf).1(nomenclature)


guid_nomeclature
rack_index


9d7e815d-4439-11ea-80e3-0cc47aace875
1.1.1

916a367c-4439-11ea-80e3-0cc47aace875
1.1.1

916a3679-4439-11ea-80e3-0cc47aace875
1.1.1

guid	name
9d7e815d-4439-11ea-80e3-0cc47aace875	Номен1
916a367c-4439-11ea-80e3-0cc47aace875	Номен2
916a3679-4439-11ea-80e3-0cc47aace875	Номен3

guid_shelf	guid_nomenclature
806deed0-532f-11e9-80df-0cc47aace875	9d7e815d-4439-11ea-80e3-0cc47aace875
806deed0-532f-11e9-80df-0cc47aace875	916a367c-4439-11ea-80e3-0cc47aace875
806deed0-532f-11e9-80df-0cc47aace875	916a3679-4439-11ea-80e3-0cc47aace875

name	name	name
Магаз1	шкаф1	Номен1
Магаз1	шкаф1	Номен2
Магаз1	шкаф1	Номен3

guid_nomeclature	rack_index
9d7e815d-4439-11ea-80e3-0cc47aace875	1.1.1
916a367c-4439-11ea-80e3-0cc47aace875	1.1.1
916a3679-4439-11ea-80e3-0cc47aace875	1.1.1