Item composition from the same SQL table

Question

Well, I started studying Mysql recently and found a question that I couldn’t solve: An ITEM table has the columns (PK)cd_item, nm_item, ds_item, cd_tipo_item, the problem in this case is that an item can be formed by the composition of 2 or 3 items or simply be unique (not composed by anything), the purpose of this is to select the item and its recipe from the database (composition made), but do not know how to solve this, I appreciate the help.

Answer 1

I had to do something similar, I did it like this: I have a table produto, where all product information is stored, this is just fake information.

CREATE TABLE IF NOT EXISTS `produto` (
  `produto_id` INT NOT NULL AUTO_INCREMENT,
  `descricao` VARCHAR(45) NULL,
  `quantidade` INT NULL,
  PRIMARY KEY (`produto_id`))
ENGINE = InnoDB;

Here we have a table of composicao where we will have reference to the table produto 2 times where produto_id will be the main product id and materia_idwill receive the id of the product that forms its composition.

CREATE TABLE IF NOT EXISTS `composicao` (
  `composicao_id` INT NOT NULL AUTO_INCREMENT,
  `produto_id` INT NOT NULL,
  `materia_id` INT NOT NULL,
  PRIMARY KEY (`composicao_id`),
  INDEX `fk_composicao_produto_idx` (`produto_id` ASC),
  INDEX `fk_composicao_produto1_idx` (`materia_id` ASC),
  CONSTRAINT `fk_composicao_produto`
    FOREIGN KEY (`produto_id`)
    REFERENCES `forum`.`produto` (`produto_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_composicao_produto1`
    FOREIGN KEY (`materia_id`)
    REFERENCES `produto` (`produto_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

In that INSERT I will give an example of 4 products, 3 that do not have composition (Wood, Screw and Nail) and one that will have composition that is the Wooden Cart.

INSERT INTO produto(descricao, quantidade) VALUES('Madeira', 1),('Parafuso', 1),('Prego',1),('Carrinho de Madeira', 1);

Here we will insert the composition of the wooden cart(Wood and Nail)

INSERT INTO composicao(produto_id, materia_id) VALUES(4,1),(4,3);

And finally to query of products.

SELECT
    p.*,
    GROUP_CONCAT(pc.descricao SEPARATOR ',') AS composicao
FROM produto p
LEFT JOIN composicao c
ON p.produto_id = c.produto_id
LEFT JOIN produto pc
ON pc.produto_id = c.materia_id
GROUP BY p.produto_id;

With the result that: