MySQL 5.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SET @@sql_mode := '';
CREATE TABLE `trm_out_receipt_header`  (
  `cash_id` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `id` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `global_number` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `local_number` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `type` bigint(20) UNSIGNED NULL DEFAULT NULL,
  `stock_id` bigint(20) UNSIGNED NULL DEFAULT NULL,
  `stock_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `client` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `card` int(11) UNSIGNED NULL DEFAULT NULL,
  `login` bigint(20) UNSIGNED NULL DEFAULT NULL,
  `shift_open` bigint(20) UNSIGNED NULL DEFAULT NULL,
  `date` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00',
  `pos` int(11) NOT NULL DEFAULT 0,
  `pos_name` varchar(80) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `sale_type` tinyint(1) UNSIGNED NULL DEFAULT NULL,
  `customer_address` varchar(80) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `version` int(11) NOT NULL DEFAULT 0,
  `deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`cash_id`, `id`) USING BTREE,
  INDEX `id`(`id`) USING BTREE,
  INDEX `date`(`date`) USING BTREE,
  INDEX `type`(`type`) USING BTREE,
  INDEX `global_number`(`global_number`) USING BTREE,
  INDEX `local_number`(`local_number`) USING BTREE,
  INDEX `shift_open`(`cash_id`, `shift_open`) USING BTREE,
  INDEX `client`(`client`) USING BTREE,
  INDEX `login`(`login`) USING BTREE,
  INDEX `versions`(`cash_id`, `version`, `deleted`) USING BTREE
);

CREATE TABLE `trm_out_receipt_payment`  (
  `cash_id` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `id` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `receipt_header` bigint(20) UNSIGNED NULL DEFAULT NULL,
  `type` bigint(20) UNSIGNED NULL DEFAULT NULL,
  `payment_id` bigint(20) UNSIGNED NULL DEFAULT NULL,
  `payment_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `pinpad_id` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `amount` decimal(20, 4) NULL DEFAULT NULL,
  `amount_with_change` decimal(20, 4) NULL DEFAULT NULL,
  `cookies` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  `link` bigint(20) UNSIGNED NULL DEFAULT NULL,
  `card_number` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `card_type` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `auth_code` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `ref_number` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `is_cash` tinyint(1) NOT NULL DEFAULT 1,
  `kkt_payment_form` tinyint(1) NOT NULL DEFAULT 0,
  `version` int(11) NOT NULL DEFAULT 0,
  `deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`cash_id`, `id`) USING BTREE,
  INDEX `id`(`id`) USING BTREE,
  INDEX `payment_id`(`payment_id`) USING BTREE,
  INDEX `payment_name`(`payment_name`) USING BTREE,
  INDEX `type`(`type`) USING BTREE,
  INDEX `receipt_header`(`cash_id`, `receipt_header`) USING BTREE,
  INDEX `amount`(`amount`) USING BTREE,
  INDEX `versions`(`cash_id`, `version`, `deleted`) USING BTREE
  );
  
CREATE TABLE `trm_out_receipt_item`  (
  `cash_id` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `id` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `receipt_header` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `var` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `item` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `var_quantity` decimal(20, 4) NULL DEFAULT NULL,
  `var_tare` decimal(20, 4) NOT NULL DEFAULT 0.0000,
  `quantity` decimal(20, 4) NULL DEFAULT NULL,
  `total_quantity` decimal(20, 4) NOT NULL DEFAULT 0.0000,
  `price` decimal(20, 4) NOT NULL DEFAULT 0.0000,
  `min_price` decimal(20, 4) NOT NULL DEFAULT 0.0000,
  `blocked_discount` tinyint(1) NOT NULL DEFAULT 0,
  `total` decimal(20, 4) NULL DEFAULT NULL,
  `discount` decimal(20, 4) NOT NULL DEFAULT 0.0000,
  `account_discount` decimal(20, 4) NOT NULL DEFAULT 0.0000,
  `stock_id` bigint(20) UNSIGNED NULL DEFAULT NULL,
  `stock_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `measurement` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `measurement_precision` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `classif` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
  `type` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `weight` bigint(20) UNSIGNED NULL DEFAULT NULL,
  `input` bigint(20) UNSIGNED NULL DEFAULT NULL,
  `tax` int(11) NULL DEFAULT NULL,
  `country` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `link_item` bigint(20) NULL DEFAULT NULL,
  `parent_item` bigint(20) NULL DEFAULT NULL,
  `position` int(11) NOT NULL DEFAULT 0,
  `remain` decimal(20, 4) NULL DEFAULT NULL,
  `pricelist` int(11) UNSIGNED NULL DEFAULT NULL,
  `cancellation_reason_id` int(11) NULL DEFAULT NULL,
  `seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  `seller_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  `blocked_add` tinyint(1) NOT NULL DEFAULT 0,
  `version` int(11) NOT NULL DEFAULT 0,
  `deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`cash_id`, `id`) USING BTREE,
  INDEX `id`(`id`) USING BTREE,
  INDEX `name`(`name`) USING BTREE,
  INDEX `link_item`(`cash_id`, `link_item`, `receipt_header`) USING BTREE,
  INDEX `type`(`type`) USING BTREE,
  INDEX `receipt_header`(`cash_id`, `receipt_header`, `id`) USING BTREE,
  INDEX `versions`(`cash_id`, `version`, `deleted`) USING BTREE,
  INDEX `seller_name`(`seller_name`) USING BTREE,
  INDEX `seller_code`(`seller_code`) USING BTREE
);

CREATE TABLE `item_groups_in_dbo`  (
  `id_group` int(11) NOT NULL,
  `tree` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
);
CREATE TABLE `items_in_dbo`  (
  `item_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `id_group` int(11) NULL DEFAULT NULL
);


INSERT INTO `trm_out_receipt_payment`(`cash_id`, `id`, `receipt_header`, `type`, `payment_id`, `payment_name`, `pinpad_id`, `amount`, `amount_with_change`, `cookies`, `link`, `card_number`, `card_type`, `auth_code`, `ref_number`, `is_cash`, `kkt_payment_form`, `version`, `deleted`) VALUES (1005001, 974568, 649061, 0, 1, 'Сбербанк', NULL, 328.4000, 328.4000, '************8296,289096,', NULL, '************8296', 'Visa', '289096', '101891742665', 1, 1, 428769, 0);
INSERT INTO `trm_out_receipt_payment`(`cash_id`, `id`, `receipt_header`, `type`, `payment_id`, `payment_name`, `pinpad_id`, `amount`, `amount_with_change`, `cookies`, `link`, `card_number`, `card_type`, `auth_code`, `ref_number`, `is_cash`, `kkt_payment_form`, `version`, `deleted`) VALUES (1005001, 974569, 649062, 0, 1, 'Сбербанк', NULL, 30.0000, 30.0000, '************8296,249682,', NULL, '************8296', 'Visa', '249682', '101850081277', 1, 1, 428770, 0);
INSERT INTO `trm_out_receipt_payment`(`cash_id`, `id`, `receipt_header`, `type`, `payment_id`, `payment_name`, `pinpad_id`, `amount`, `amount_with_change`, `cookies`, `link`, `card_number`, `card_type`, `auth_code`, `ref_number`, `is_cash`, `kkt_payment_form`, `version`, `deleted`) VALUES (1005001, 974612, 649092, 0, 1, 'Сбербанк', NULL, 203.6900, 203.6900, '************8296,266173,', NULL, '************8296', 'Visa', '266173', '101850616792', 1, 1, 428800, 0);
INSERT INTO `trm_out_receipt_payment`(`cash_id`, `id`, `receipt_header`, `type`, `payment_id`, `payment_name`, `pinpad_id`, `amount`, `amount_with_change`, `cookies`, `link`, `card_number`, `card_type`, `auth_code`, `ref_number`, `is_cash`, `kkt_payment_form`, `version`, `deleted`) VALUES (1005001, 974795, 649233, 0, 1, 'Сбербанк', NULL, 709.7000, 709.7000, '************8296,283367,', NULL, '************8296', 'Visa', '283367', '101982825938', 1, 1, 428937, 0);
INSERT INTO `trm_out_receipt_payment`(`cash_id`, `id`, `receipt_header`, `type`, `payment_id`, `payment_name`, `pinpad_id`, `amount`, `amount_with_change`, `cookies`, `link`, `card_number`, `card_type`, `auth_code`, `ref_number`, `is_cash`, `kkt_payment_form`, `version`, `deleted`) VALUES (1005001, 974796, 649234, 0, 1, 'Сбербанк', NULL, 626.2500, 626.2500, '************8296,205489,', NULL, '************8296', 'Visa', '205489', '101983056051', 1, 1, 428938, 0);


INSERT INTO `trm_out_receipt_header`(`cash_id`, `id`, `global_number`, `local_number`, `type`, `stock_id`, `stock_name`, `client`, `card`, `login`, `shift_open`, `date`, `pos`, `pos_name`, `sale_type`, `customer_address`, `version`, `deleted`) VALUES (1, 649061, 651512, 1, 0, 0, '', '{34315ba0-5834-4786-b49c-03d23a5e5815}', 229456, 7019, 2927, '2021-01-18 10:16:02', 7, 'Терминал №7', 0, NULL, 432478, 0);
INSERT INTO `trm_out_receipt_header`(`cash_id`, `id`, `global_number`, `local_number`, `type`, `stock_id`, `stock_name`, `client`, `card`, `login`, `shift_open`, `date`, `pos`, `pos_name`, `sale_type`, `customer_address`, `version`, `deleted`) VALUES (1005001, 649062, 651513, 2, 0, 0, '', '{34315ba0-5834-4786-b49c-03d23a5e5815}', 229456, 7019, 2927, '2021-01-18 13:38:48', 7, 'Терминал №7', 0, NULL, 432479, 0);
INSERT INTO `trm_out_receipt_header`(`cash_id`, `id`, `global_number`, `local_number`, `type`, `stock_id`, `stock_name`, `client`, `card`, `login`, `shift_open`, `date`, `pos`, `pos_name`, `sale_type`, `customer_address`, `version`, `deleted`) VALUES (1005001, 649092, 651543, 32, 0, 0, '', '{34315ba0-5834-4786-b49c-03d23a5e5815}', 229456, 7019, 2927, '2021-01-18 15:29:47', 7, 'Терминал №7', 0, NULL, 432509, 0);
INSERT INTO `trm_out_receipt_header`(`cash_id`, `id`, `global_number`, `local_number`, `type`, `stock_id`, `stock_name`, `client`, `card`, `login`, `shift_open`, `date`, `pos`, `pos_name`, `sale_type`, `customer_address`, `version`, `deleted`) VALUES (1005001, 649233, 651685, 1, 0, 0, '', '{34315ba0-5834-4786-b49c-03d23a5e5815}', 229456, 7021, 2928, '2021-01-19 11:44:42', 7, 'Терминал №7', 0, NULL, 432649, 0);
INSERT INTO `trm_out_receipt_header`(`cash_id`, `id`, `global_number`, `local_number`, `type`, `stock_id`, `stock_name`, `client`, `card`, `login`, `shift_open`, `date`, `pos`, `pos_name`, `sale_type`, `customer_address`, `version`, `deleted`) VALUES (1005001, 649234, 651686, 2, 0, 0, '', '{34315ba0-5834-4786-b49c-03d23a5e5815}', 229456, 7021, 2928, '2021-01-19 13:32:27', 7, 'Терминал №7', 0, NULL, 432650, 0);


INSERT INTO `trm_out_receipt_item`(`cash_id`, `id`, `receipt_header`, `var`, `item`, `name`, `var_quantity`, `var_tare`, `quantity`, `total_quantity`, `price`, `min_price`, `blocked_discount`, `total`, `discount`, `account_discount`, `stock_id`, `stock_name`, `measurement`, `measurement_precision`, `classif`, `type`, `weight`, `input`, `tax`, `country`, `link_item`, `parent_item`, `position`, `remain`, `pricelist`, `cancellation_reason_id`, `seller_name`, `seller_code`, `blocked_add`, `version`, `deleted`) VALUES (1005001, 1397909, 649061, '2602882', 'Эр000045689', 'Помидоры Узбекистан Ш', 1.0000, 0.0000, 1.1120, 1.1120, 200.0000, 0.0000, 0, 222.4000, 0.0000, 0.0000, 0, '', 'кг', 3, '991', 0, 0, 0, 5, NULL, NULL, NULL, 0, NULL, 89, NULL, '', '', 0, 461818, 0);
INSERT INTO `trm_out_receipt_item`(`cash_id`, `id`, `receipt_header`, `var`, `item`, `name`, `var_quantity`, `var_tare`, `quantity`, `total_quantity`, `price`, `min_price`, `blocked_discount`, `total`, `discount`, `account_discount`, `stock_id`, `stock_name`, `measurement`, `measurement_precision`, `classif`, `type`, `weight`, `input`, `tax`, `country`, `link_item`, `parent_item`, `position`, `remain`, `pricelist`, `cancellation_reason_id`, `seller_name`, `seller_code`, `blocked_add`, `version`, `deleted`) VALUES (1005001, 1397910, 649061, '2602730', 'ОВ000000413', 'Огурцы Тепличные Россия', 1.0000, 0.0000, 0.4240, 0.4240, 250.0000, 0.0000, 0, 106.0000, 0.0000, 0.0000, 0, '', 'кг', 3, '622', 0, 0, 0, 5, NULL, NULL, NULL, 1, NULL, 89, NULL, '', '', 0, 461818, 0);
INSERT INTO `trm_out_receipt_item`(`cash_id`, `id`, `receipt_header`, `var`, `item`, `name`, `var_quantity`, `var_tare`, `quantity`, `total_quantity`, `price`, `min_price`, `blocked_discount`, `total`, `discount`, `account_discount`, `stock_id`, `stock_name`, `measurement`, `measurement_precision`, `classif`, `type`, `weight`, `input`, `tax`, `country`, `link_item`, `parent_item`, `position`, `remain`, `pricelist`, `cancellation_reason_id`, `seller_name`, `seller_code`, `blocked_add`, `version`, `deleted`) VALUES (1005001, 1397911, 649062, '2500000747847', '307043', 'Укроп, шт', 1.0000, 0.0000, 1.0000, 1.0000, 15.0000, 0.0000, 0, 15.0000, 0.0000, 0.0000, 0, '', 'шт', 0, '268', 0, 0, 0, 5, NULL, NULL, NULL, 0, NULL, 89, NULL, '', '', 0, 461819, 0);
INSERT INTO `trm_out_receipt_item`(`cash_id`, `id`, `receipt_header`, `var`, `item`, `name`, `var_quantity`, `var_tare`, `quantity`, `total_quantity`, `price`, `min_price`, `blocked_discount`, `total`, `discount`, `account_discount`, `stock_id`, `stock_name`, `measurement`, `measurement_precision`, `classif`, `type`, `weight`, `input`, `tax`, `country`, `link_item`, `parent_item`, `position`, `remain`, `pricelist`, `cancellation_reason_id`, `seller_name`, `seller_code`, `blocked_add`, `version`, `deleted`) VALUES (1005001, 1397912, 649062, '2500000207457', '074536', 'Петрушка шт', 1.0000, 0.0000, 1.0000, 1.0000, 15.0000, 0.0000, 0, 15.0000, 0.0000, 0.0000, 0, '', 'шт', 0, '268', 0, 0, 0, 5, NULL, NULL, NULL, 1, NULL, 89, NULL, '', '', 0, 461819, 0);
INSERT INTO `trm_out_receipt_item`(`cash_id`, `id`, `receipt_header`, `var`, `item`, `name`, `var_quantity`, `var_tare`, `quantity`, `total_quantity`, `price`, `min_price`, `blocked_discount`, `total`, `discount`, `account_discount`, `stock_id`, `stock_name`, `measurement`, `measurement_precision`, `classif`, `type`, `weight`, `input`, `tax`, `country`, `link_item`, `parent_item`, `position`, `remain`, `pricelist`, `cancellation_reason_id`, `seller_name`, `seller_code`, `blocked_add`, `version`, `deleted`) VALUES (1005001, 1397969, 649092, '2601554', 'ОВ000000273', 'Капуста Россия вес', 1.0000, 0.0000, 1.7560, 1.7560, 50.0000, 0.0000, 0, 87.8000, 0.0000, 0.0000, 0, '', 'кг', 3, '320', 0, 0, 0, 5, NULL, NULL, NULL, 0, NULL, 89, NULL, '', '', 0, 461849, 0);
INSERT INTO `trm_out_receipt_item`(`cash_id`, `id`, `receipt_header`, `var`, `item`, `name`, `var_quantity`, `var_tare`, `quantity`, `total_quantity`, `price`, `min_price`, `blocked_discount`, `total`, `discount`, `account_discount`, `stock_id`, `stock_name`, `measurement`, `measurement_precision`, `classif`, `type`, `weight`, `input`, `tax`, `country`, `link_item`, `parent_item`, `position`, `remain`, `pricelist`, `cancellation_reason_id`, `seller_name`, `seller_code`, `blocked_add`, `version`, `deleted`) VALUES (1005001, 1397970, 649092, '2602364', 'ОВ000000555', 'Свекла Россия У', 1.0000, 0.0000, 0.5380, 0.5380, 60.0000, 0.0000, 0, 32.2800, 0.0000, 0.0000, 0, '', 'кг', 3, '825', 0, 0, 0, 5, NULL, NULL, NULL, 1, NULL, 89, NULL, '', '', 0, 461849, 0);
INSERT INTO `trm_out_receipt_item`(`cash_id`, `id`, `receipt_header`, `var`, `item`, `name`, `var_quantity`, `var_tare`, `quantity`, `total_quantity`, `price`, `min_price`, `blocked_discount`, `total`, `discount`, `account_discount`, `stock_id`, `stock_name`, `measurement`, `measurement_precision`, `classif`, `type`, `weight`, `input`, `tax`, `country`, `link_item`, `parent_item`, `position`, `remain`, `pricelist`, `cancellation_reason_id`, `seller_name`, `seller_code`, `blocked_add`, `version`, `deleted`) VALUES (1005001, 1397971, 649092, '2627734', '325001', 'Морковь с/у', 1.0000, 0.0000, 0.5580, 0.5580, 75.0000, 0.0000, 0, 41.8500, 0.0000, 0.0000, 0, '', 'кг', 3, '527', 0, 0, 0, 5, NULL, NULL, NULL, 2, NULL, 89, NULL, '', '', 0, 461849, 0);
INSERT INTO `trm_out_receipt_item`(`cash_id`, `id`, `receipt_header`, `var`, `item`, `name`, `var_quantity`, `var_tare`, `quantity`, `total_quantity`, `price`, `min_price`, `blocked_discount`, `total`, `discount`, `account_discount`, `stock_id`, `stock_name`, `measurement`, `measurement_precision`, `classif`, `type`, `weight`, `input`, `tax`, `country`, `link_item`, `parent_item`, `position`, `remain`, `pricelist`, `cancellation_reason_id`, `seller_name`, `seller_code`, `blocked_add`, `version`, `deleted`) VALUES (1005001, 1397972, 649092, '2602363', 'ОВ000000805', 'Лук Россия', 1.0000, 0.0000, 0.9280, 0.9280, 45.0000, 0.0000, 0, 41.7600, 0.0000, 0.0000, 0, '', 'кг', 3, '474', 0, 0, 0, 5, NULL, NULL, NULL, 3, NULL, 89, NULL, '', '', 0, 461849, 0);
INSERT INTO `trm_out_receipt_item`(`cash_id`, `id`, `receipt_header`, `var`, `item`, `name`, `var_quantity`, `var_tare`, `quantity`, `total_quantity`, `price`, `min_price`, `blocked_discount`, `total`, `discount`, `account_discount`, `stock_id`, `stock_name`, `measurement`, `measurement_precision`, `classif`, `type`, `weight`, `input`, `tax`, `country`, `link_item`, `parent_item`, `position`, `remain`, `pricelist`, `cancellation_reason_id`, `seller_name`, `seller_code`, `blocked_add`, `version`, `deleted`) VALUES (1005001, 1398270, 649233, '2602487', 'Эр000044037', 'Перец вес Красный', 1.0000, 0.0000, 0.6440, 0.6440, 450.0000, 0.0000, 0, 289.8000, 0.0000, 0.0000, 0, '', 'кг', 3, '669', 0, 0, 0, 5, NULL, NULL, NULL, 0, NULL, 89, NULL, '', '', 0, 461989, 0);
INSERT INTO `trm_out_receipt_item`(`cash_id`, `id`, `receipt_header`, `var`, `item`, `name`, `var_quantity`, `var_tare`, `quantity`, `total_quantity`, `price`, `min_price`, `blocked_discount`, `total`, `discount`, `account_discount`, `stock_id`, `stock_name`, `measurement`, `measurement_precision`, `classif`, `type`, `weight`, `input`, `tax`, `country`, `link_item`, `parent_item`, `position`, `remain`, `pricelist`, `cancellation_reason_id`, `seller_name`, `seller_code`, `blocked_add`, `version`, `deleted`) VALUES (1005001, 1398271, 649233, '2601182', 'ОВ000000714', 'Чеснок вес', 1.0000, 0.0000, 0.3160, 0.3160, 300.0000, 0.0000, 0, 94.8000, 0.0000, 0.0000, 0, '', 'кг', 3, '474', 0, 0, 0, 5, NULL, NULL, NULL, 1, NULL, 89, NULL, '', '', 0, 461989, 0);
INSERT INTO `trm_out_receipt_item`(`cash_id`, `id`, `receipt_header`, `var`, `item`, `name`, `var_quantity`, `var_tare`, `quantity`, `total_quantity`, `price`, `min_price`, `blocked_discount`, `total`, `discount`, `account_discount`, `stock_id`, `stock_name`, `measurement`, `measurement_precision`, `classif`, `type`, `weight`, `input`, `tax`, `country`, `link_item`, `parent_item`, `position`, `remain`, `pricelist`, `cancellation_reason_id`, `seller_name`, `seller_code`, `blocked_add`, `version`, `deleted`) VALUES (1005001, 1398272, 649233, '2625375', '307370', 'Изюм коричневый 1/10кг Золотая Азия ', 1.0000, 0.0000, 0.3180, 0.3180, 450.0000, 0.0000, 0, 143.1000, 0.0000, 0.0000, 0, '', 'кг', 3, '1113', 0, 0, 0, 5, NULL, NULL, NULL, 2, NULL, 89, NULL, '', '', 0, 461989, 0);
INSERT INTO `trm_out_receipt_item`(`cash_id`, `id`, `receipt_header`, `var`, `item`, `name`, `var_quantity`, `var_tare`, `quantity`, `total_quantity`, `price`, `min_price`, `blocked_discount`, `total`, `discount`, `account_discount`, `stock_id`, `stock_name`, `measurement`, `measurement_precision`, `classif`, `type`, `weight`, `input`, `tax`, `country`, `link_item`, `parent_item`, `position`, `remain`, `pricelist`, `cancellation_reason_id`, `seller_name`, `seller_code`, `blocked_add`, `version`, `deleted`) VALUES (1005001, 1398273, 649233, '2605277', '015681', 'Орех Грецкий очищенный вес Узбекистан ', 1.0000, 0.0000, 0.1820, 0.1820, 1000.0000, 0.0000, 0, 182.0000, 0.0000, 0.0000, 0, '', 'кг', 3, '632', 0, 0, 0, 5, NULL, NULL, NULL, 3, NULL, 89, NULL, '', '', 0, 461989, 0);
INSERT INTO `trm_out_receipt_item`(`cash_id`, `id`, `receipt_header`, `var`, `item`, `name`, `var_quantity`, `var_tare`, `quantity`, `total_quantity`, `price`, `min_price`, `blocked_discount`, `total`, `discount`, `account_discount`, `stock_id`, `stock_name`, `measurement`, `measurement_precision`, `classif`, `type`, `weight`, `input`, `tax`, `country`, `link_item`, `parent_item`, `position`, `remain`, `pricelist`, `cancellation_reason_id`, `seller_name`, `seller_code`, `blocked_add`, `version`, `deleted`) VALUES (1005001, 1398274, 649234, '2627734', '325001', 'Морковь с/у', 1.0000, 0.0000, 1.1060, 1.1060, 75.0000, 0.0000, 0, 82.9500, 0.0000, 0.0000, 0, '', 'кг', 3, '527', 0, 0, 0, 5, NULL, NULL, NULL, 0, NULL, 89, NULL, '', '', 0, 461990, 0);
INSERT INTO `trm_out_receipt_item`(`cash_id`, `id`, `receipt_header`, `var`, `item`, `name`, `var_quantity`, `var_tare`, `quantity`, `total_quantity`, `price`, `min_price`, `blocked_discount`, `total`, `discount`, `account_discount`, `stock_id`, `stock_name`, `measurement`, `measurement_precision`, `classif`, `type`, `weight`, `input`, `tax`, `country`, `link_item`, `parent_item`, `position`, `remain`, `pricelist`, `cancellation_reason_id`, `seller_name`, `seller_code`, `blocked_add`, `version`, `deleted`) VALUES (1005001, 1398275, 649234, '2602487', 'Эр000044037', 'Перец вес Красный', 1.0000, 0.0000, 0.4640, 0.4640, 450.0000, 0.0000, 0, 208.8000, 0.0000, 0.0000, 0, '', 'кг', 3, '669', 0, 0, 0, 5, NULL, NULL, NULL, 1, NULL, 89, NULL, '', '', 0, 461990, 0);
INSERT INTO `trm_out_receipt_item`(`cash_id`, `id`, `receipt_header`, `var`, `item`, `name`, `var_quantity`, `var_tare`, `quantity`, `total_quantity`, `price`, `min_price`, `blocked_discount`, `total`, `discount`, `account_discount`, `stock_id`, `stock_name`, `measurement`, `measurement_precision`, `classif`, `type`, `weight`, `input`, `tax`, `country`, `link_item`, `parent_item`, `position`, `remain`, `pricelist`, `cancellation_reason_id`, `seller_name`, `seller_code`, `blocked_add`, `version`, `deleted`) VALUES (1005001, 1398276, 649234, '2601182', 'ОВ000000714', 'Чеснок вес', 1.0000, 0.0000, 0.1520, 0.1520, 300.0000, 0.0000, 0, 45.6000, 0.0000, 0.0000, 0, '', 'кг', 3, '474', 0, 0, 0, 5, NULL, NULL, NULL, 2, NULL, 89, NULL, '', '', 0, 461990, 0);
INSERT INTO `trm_out_receipt_item`(`cash_id`, `id`, `receipt_header`, `var`, `item`, `name`, `var_quantity`, `var_tare`, `quantity`, `total_quantity`, `price`, `min_price`, `blocked_discount`, `total`, `discount`, `account_discount`, `stock_id`, `stock_name`, `measurement`, `measurement_precision`, `classif`, `type`, `weight`, `input`, `tax`, `country`, `link_item`, `parent_item`, `position`, `remain`, `pricelist`, `cancellation_reason_id`, `seller_name`, `seller_code`, `blocked_add`, `version`, `deleted`) VALUES (1005001, 1398277, 649234, '2602882', 'Эр000045689', 'Помидоры Узбекистан Ш', 1.0000, 0.0000, 0.8940, 0.8940, 200.0000, 0.0000, 0, 178.8000, 0.0000, 0.0000, 0, '', 'кг', 3, '991', 0, 0, 0, 5, NULL, NULL, NULL, 3, NULL, 89, NULL, '', '', 0, 461990, 0);
INSERT INTO `trm_out_receipt_item`(`cash_id`, `id`, `receipt_header`, `var`, `item`, `name`, `var_quantity`, `var_tare`, `quantity`, `total_quantity`, `price`, `min_price`, `blocked_discount`, `total`, `discount`, `account_discount`, `stock_id`, `stock_name`, `measurement`, `measurement_precision`, `classif`, `type`, `weight`, `input`, `tax`, `country`, `link_item`, `parent_item`, `position`, `remain`, `pricelist`, `cancellation_reason_id`, `seller_name`, `seller_code`, `blocked_add`, `version`, `deleted`) VALUES (1005001, 1398278, 649234, '2606718', '027091', 'Яблоки Семеринка Узбекистан', 1.0000, 0.0000, 0.7340, 0.7340, 150.0000, 0.0000, 0, 110.1000, 0.0000, 0.0000, 0, '', 'кг', 3, '1110', 0, 0, 0, 5, NULL, NULL, NULL, 4, NULL, 89, NULL, '', '', 0, 461990, 0);


INSERT INTO `items_in_dbo`(`item_id`, `id_group`) VALUES ('307370', 1113);
INSERT INTO `items_in_dbo`(`item_id`, `id_group`) VALUES ('027091', 1110);
INSERT INTO `items_in_dbo`(`item_id`, `id_group`) VALUES ('Эр000045689', 991);
INSERT INTO `items_in_dbo`(`item_id`, `id_group`) VALUES ('ОВ000000555', 825);
INSERT INTO `items_in_dbo`(`item_id`, `id_group`) VALUES ('Эр000044037', 669);
INSERT INTO `items_in_dbo`(`item_id`, `id_group`) VALUES ('015681', 632);
INSERT INTO `items_in_dbo`(`item_id`, `id_group`) VALUES ('ОВ000000413', 622);
INSERT INTO `items_in_dbo`(`item_id`, `id_group`) VALUES ('325001', 527);
INSERT INTO `items_in_dbo`(`item_id`, `id_group`) VALUES ('ОВ000000714', 474);
INSERT INTO `items_in_dbo`(`item_id`, `id_group`) VALUES ('ОВ000000805', 474);
INSERT INTO `items_in_dbo`(`item_id`, `id_group`) VALUES ('ОВ000000273', 320);
INSERT INTO `items_in_dbo`(`item_id`, `id_group`) VALUES ('307043', 268);
INSERT INTO `items_in_dbo`(`item_id`, `id_group`) VALUES ('074536', 268);
INSERT INTO `items_in_dbo`(`item_id`, `id_group`) VALUES ('О-000015681', 162);
INSERT INTO `items_in_dbo`(`item_id`, `id_group`) VALUES ('Ц-000156816', 0);
INSERT INTO `items_in_dbo`(`item_id`, `id_group`) VALUES ('Миос0015681', 0);


INSERT INTO `item_groups_in_dbo`(`id_group`, `tree`) VALUES (162, '1.11.1.4.');
INSERT INTO `item_groups_in_dbo`(`id_group`, `tree`) VALUES (268, '1.8.6.1.');
INSERT INTO `item_groups_in_dbo`(`id_group`, `tree`) VALUES (320, '1.8.4.9.');
INSERT INTO `item_groups_in_dbo`(`id_group`, `tree`) VALUES (474, '1.8.4.2.');
INSERT INTO `item_groups_in_dbo`(`id_group`, `tree`) VALUES (527, '1.8.4.3.');
INSERT INTO `item_groups_in_dbo`(`id_group`, `tree`) VALUES (622, '1.8.4.5.');
INSERT INTO `item_groups_in_dbo`(`id_group`, `tree`) VALUES (632, '1.8.3.6.');
INSERT INTO `item_groups_in_dbo`(`id_group`, `tree`) VALUES (669, '1.8.4.4.');
INSERT INTO `item_groups_in_dbo`(`id_group`, `tree`) VALUES (825, '1.8.4.7.');
INSERT INTO `item_groups_in_dbo`(`id_group`, `tree`) VALUES (991, '1.8.4.6.');
INSERT INTO `item_groups_in_dbo`(`id_group`, `tree`) VALUES (1110, '1.8.1.1.');
INSERT INTO `item_groups_in_dbo`(`id_group`, `tree`) VALUES (1113, '1.8.3.2.');
INSERT INTO `item_groups_in_dbo`(`id_group`, `tree`) VALUES (1320, '13.5.');
INSERT INTO `item_groups_in_dbo`(`id_group`, `tree`) VALUES (1527, '9.2.1.4.');

EXPLAIN

SELECT card, pos, shift_open, local_number, 
       date, amount, tori.item, tori.quantity, 
       tori.receipt_header 
FROM trm_out_receipt_header torh   # к этой таблицы прикрепим две остальные по двум разным условиям совпадения
                                   # 2-ю таблицу по одному условию:
LEFT JOIN trm_out_receipt_item tori  ON (torh.id=tori.receipt_header) 
                                   # 3-ю по другому:
LEFT JOIN trm_out_receipt_payment torp ON (torh.id=torp.receipt_header) 
where torh.card IN ('229456', '229455') 
  and tori.cash_id in ('1005001', '1005004', '201002001', '201002005', '201002001', '201002003', '201002004', '201002005');


id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra


1
SIMPLE
tori
ALL
PRIMARY,link_item,receipt_header,versions
null
null
null
17
Using where

1
SIMPLE
torh
ALL
id
null
null
null
5
Using where; Using join buffer (Block Nested Loop)

1
SIMPLE
torp
ALL
null
null
null
null
5
Using where; Using join buffer (Block Nested Loop)


-- CREATE INDEX torh_idx ON trm_out_receipt_header (id);
CREATE INDEX torh_idx2 ON trm_out_receipt_header (card);
CREATE INDEX torh_idx3 ON trm_out_receipt_header (card, id);
CREATE INDEX torh_idx4 ON trm_out_receipt_header (id, card);

CREATE INDEX tori_idx ON trm_out_receipt_item (receipt_header);
-- CREATE INDEX tori_idx2 ON trm_out_receipt_item (cash_id);
-- CREATE INDEX tori_idx3 ON trm_out_receipt_item (cash_id, receipt_header);
CREATE INDEX tori_idx4 ON trm_out_receipt_item (receipt_header, cash_id);

CREATE INDEX torp_idx ON trm_out_receipt_payment (receipt_header);
SELECT card, pos, shift_open, local_number, 
       date, amount, tori.item, tori.quantity, 
       tori.receipt_header, igid.tree, igid.id_group 
FROM trm_out_receipt_header torh   
INNER JOIN trm_out_receipt_item tori  ON (torh.id=tori.receipt_header and torh.cash_id = tori.cash_id) 
LEFT JOIN trm_out_receipt_payment torp ON (torh.id=torp.receipt_header and torh.cash_id=torp.cash_id) 
LEFT JOIN items_in_dbo iid ON (iid.item_id = tori.item)
LEFT JOIN item_groups_in_dbo igid ON (igid.id_group = iid.id_group)
where torh.card IN ('229456', '229455') 
group by date


card
pos
shift_open
local_number
date
amount
item
quantity
receipt_header
tree
id_group


229456
7
2927
2
2021-01-18 13:38:48
30.0000
307043
1.0000
649062
1.8.6.1.
268

229456
7
2927
32
2021-01-18 15:29:47
203.6900
ОВ000000273
1.7560
649092
1.8.4.9.
320

229456
7
2928
1
2021-01-19 11:44:42
709.7000
ОВ000000714
0.3160
649233
1.8.4.2.
474

229456
7
2928
2
2021-01-19 13:32:27
626.2500
ОВ000000714
0.1520
649234
1.8.4.2.
474
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	tori	ALL	PRIMARY,link_item,receipt_header,versions	null	null	null	17	Using where
1	SIMPLE	torh	ALL	id	null	null	null	5	Using where; Using join buffer (Block Nested Loop)
1	SIMPLE	torp	ALL	null	null	null	null	5	Using where; Using join buffer (Block Nested Loop)
card	pos	shift_open	local_number	date	amount	item	quantity	receipt_header	tree	id_group
229456	7	2927	2	2021-01-18 13:38:48	30.0000	307043	1.0000	649062	1.8.6.1.	268
229456	7	2927	32	2021-01-18 15:29:47	203.6900	ОВ000000273	1.7560	649092	1.8.4.9.	320
229456	7	2928	1	2021-01-19 11:44:42	709.7000	ОВ000000714	0.3160	649233	1.8.4.2.	474
229456	7	2928	2	2021-01-19 13:32:27	626.2500	ОВ000000714	0.1520	649234	1.8.4.2.	474