Соединить элемент таблицы с другим элементом той же таблицы - MySQL

-- MySQL Workbench Forward Engineering
 
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
 
-- -----------------------------------------------------
-- Schema shop
-- -----------------------------------------------------
 
-- -----------------------------------------------------
-- Schema shop
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `shop` DEFAULT CHARACTER SET utf8 ;
USE `shop` ;
 
-- -----------------------------------------------------
-- Table `shop`.`customer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `shop`.`customer` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(45) NULL,
  `last_name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `shop`.`the_order`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `shop`.`the_order` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `client_id` INT NOT NULL,
  `time` VARCHAR(5) NULL,
  `date` DATE NULL,
  PRIMARY KEY (`id`, `client_id`),
  INDEX `client_id_idx` (`client_id` ASC),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  CONSTRAINT `client_id`
    FOREIGN KEY (`client_id`)
    REFERENCES `shop`.`customer` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `shop`.`category`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `shop`.`category` (
  `id` INT NOT NULL,
  `name` VARCHAR(30) NOT NULL,
  `parent_cat` VARCHAR(30) NOT NULL,
  PRIMARY KEY (`id`, `parent_cat`),
  INDEX `subcat_to_cat_idx` (`parent_cat` ASC),
  CONSTRAINT `subcat_to_cat`
    FOREIGN KEY (`parent_cat`)
    REFERENCES `shop`.`category` (`name`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `shop`.`product`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `shop`.`product` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `price` INT NULL,
  `weight` FLOAT NULL,
  `cat_id` INT NOT NULL,
  PRIMARY KEY (`id`, `cat_id`),
  INDEX `productCategory_idx` (`cat_id` ASC),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  CONSTRAINT `productCategory`
    FOREIGN KEY (`cat_id`)
    REFERENCES `shop`.`category` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `shop`.`orderProduct`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `shop`.`orderProduct` (
  `order_id` INT NOT NULL,
  `product_id` INT NOT NULL,
  PRIMARY KEY (`order_id`, `product_id`),
  INDEX `product_id_idx` (`product_id` ASC),
  CONSTRAINT `order_id`
    FOREIGN KEY (`order_id`)
    REFERENCES `shop`.`the_order` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `product_id`
    FOREIGN KEY (`product_id`)
    REFERENCES `shop`.`product` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
 
-- -----------------------------------------------------
-- Data for table `shop`.`customer`
-- -----------------------------------------------------
START TRANSACTION;
USE `shop`;
INSERT INTO `shop`.`customer` (`id`, `first_name`, `last_name`) VALUES (1, 'Grisha', 'Sydorenko');
INSERT INTO `shop`.`customer` (`id`, `first_name`, `last_name`) VALUES (2, 'Pasha', 'Grigorenko');
INSERT INTO `shop`.`customer` (`id`, `first_name`, `last_name`) VALUES (3, 'Sasha', 'Mukolenko');
INSERT INTO `shop`.`customer` (`id`, `first_name`, `last_name`) VALUES (4, 'Dasha', 'Denisenko');
INSERT INTO `shop`.`customer` (`id`, `first_name`, `last_name`) VALUES (5, 'Masha', 'Maximenko');
 
COMMIT;

-- -----------------------------------------------------
-- Data for table `shop`.`the_order`
-- -----------------------------------------------------
START TRANSACTION;
USE `shop`;
INSERT INTO `shop`.`the_order` (`id`, `client_id`, `time`, `date`) VALUES (1, 5, '', '28/08/2017');
INSERT INTO `shop`.`the_order` (`id`, `client_id`, `time`, `date`) VALUES (2, 1, '', '27/08/2017');
INSERT INTO `shop`.`the_order` (`id`, `client_id`, `time`, `date`) VALUES (3, 1, '', '26/08/2017');
INSERT INTO `shop`.`the_order` (`id`, `client_id`, `time`, `date`) VALUES (4, 4, '', '25/08/2017');
INSERT INTO `shop`.`the_order` (`id`, `client_id`, `time`, `date`) VALUES (5, 5, '', '24/08/2017');
 
COMMIT;

-- -----------------------------------------------------
-- Data for table `shop`.`category`
-- -----------------------------------------------------
START TRANSACTION;
USE `shop`;
INSERT INTO `shop`.`category` (`id`, `name`, `parent_cat`) VALUES (1, 'Laptops', 'null');
INSERT INTO `shop`.`category` (`id`, `name`, `parent_cat`) VALUES (2, 'Phones', 'null');
INSERT INTO `shop`.`category` (`id`, `name`, `parent_cat`) VALUES (3, 'Household appliances', 'null');
INSERT INTO `shop`.`category` (`id`, `name`, `parent_cat`) VALUES (4, 'Goods for the home', 'null');
INSERT INTO `shop`.`category` (`id`, `name`, `parent_cat`) VALUES (5, 'Plumbing and repair', 'null');
INSERT INTO `shop`.`category` (`id`, `name`, `parent_cat`) VALUES (6, 'Asus', 'Laptops');
INSERT INTO `shop`.`category` (`id`, `name`, `parent_cat`) VALUES (7, 'Acer', 'Laptops');
INSERT INTO `shop`.`category` (`id`, `name`, `parent_cat`) VALUES (8, 'Nokia', 'Phones');
INSERT INTO `shop`.`category` (`id`, `name`, `parent_cat`) VALUES (9, 'Samsung', 'Phones');
 
COMMIT;

-- -----------------------------------------------------
-- Data for table `shop`.`product`
-- -----------------------------------------------------
START TRANSACTION;
USE `shop`;
INSERT INTO `shop`.`product` (`id`, `name`, `price`, `weight`, `cat_id`) VALUES (1, 'product-1', 320, 5.5, 5);
INSERT INTO `shop`.`product` (`id`, `name`, `price`, `weight`, `cat_id`) VALUES (2, 'product-2', 450, 2.8, 4);
INSERT INTO `shop`.`product` (`id`, `name`, `price`, `weight`, `cat_id`) VALUES (3, 'product-3', 1200, 3.3, 3);
INSERT INTO `shop`.`product` (`id`, `name`, `price`, `weight`, `cat_id`) VALUES (4, 'product-4', 280, 10.2, 4);
INSERT INTO `shop`.`product` (`id`, `name`, `price`, `weight`, `cat_id`) VALUES (5, 'product-5', 785, 7.4, 1);
 
COMMIT;

-- -----------------------------------------------------
-- Data for table `shop`.`orderProduct`
-- -----------------------------------------------------
START TRANSACTION;
USE `shop`;
INSERT INTO `shop`.`orderProduct` (`order_id`, `product_id`) VALUES (1, 2);
INSERT INTO `shop`.`orderProduct` (`order_id`, `product_id`) VALUES (2, 2);
INSERT INTO `shop`.`orderProduct` (`order_id`, `product_id`) VALUES (3, 1);
 
COMMIT;

        -- -----------------------------------------------------
        -- Table `shop`.`category`
        -- -----------------------------------------------------
        CREATE TABLE IF NOT EXISTS `shop`.`category` (
          `id` INT NOT NULL,
          `name` VARCHAR(30) NOT NULL,
          `parent_cat` VARCHAR(30) NOT NULL,
          PRIMARY KEY (`id`, `parent_cat`),
          INDEX `subcat_to_cat_idx` (`parent_cat` ASC),
          CONSTRAINT `subcat_to_cat`
            FOREIGN KEY (`parent_cat`)
            REFERENCES `shop`.`category` (`name`)
            ON DELETE CASCADE
            ON UPDATE CASCADE)
        ENGINE = InnoDB

        INSERT INTO `shop`.`category` (`id`, `name`, `parent_cat`) VALUES (1, 'Laptops', NULL)