Return PHP categories/subcategories records

Question

I have the following structure of tables and records:

CREATE TABLE IF NOT EXISTS `produto` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_categoria` text,
  `titulo` text,
  `referencia` text,
  `valor` decimal(10,0) DEFAULT NULL,
  `imagem_principal` text,
  `descricao` text,
  `data` text,
  `status` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=3 ;


INSERT INTO `produto` (`id`, `id_categoria`, `titulo`, `referencia`, `valor`, `imagem_principal`, `descricao`, `data`, `status`) VALUES
(1, '5', '4Y1A7316-S169', '4Y1A7316-S169', '0', 'd6663aa43c2c044e90375d312b601599.png', '<p>Pincel para Cílios&nbsp;&nbsp;&nbsp;&nbsp;</p>', '2019-04-17 18:02:24', 0),
(2, '5', '5Y1A7316-S621', '5Y1A7316-S621', '0', '7f699d3c34fd63b8810b1a457404c317.png', '<p>5Y1A7316-S621<br></p>', '2019-04-22 17:47:24', 0);


CREATE TABLE IF NOT EXISTS `produto_categoria` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_categoria` text,
  `titulo` text,
  `ordem` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=13 ;


INSERT INTO `produto_categoria` (`id`, `id_categoria`, `titulo`, `ordem`) VALUES
(1, '0', 'Cílios', '0'),
(2, '0', 'Pentes', '2'),
(3, '0', 'Toucas', '4'),
(4, '1', 'Fio a Fio', '0'),
(5, '1', '3D', '1'),
(6, '0', 'Pinças', '0'),
(7, '0', 'Pincéis', '0'),
(8, '0', 'Tesouras', '0'),
(9, '0', 'Unhas', '0'),
(10, '0', 'Esponjas', '0'),
(11, '0', 'Espátula', '0'),
(12, '0', 'Alicates', '0');

Note that I have two records in the product table, with id_category 5. Subcategory that belongs to the eyelash category (1).

I need to elaborate an SQL where I can bring the records, regardless of the subcategeoria. In this case, I need the result to return the two products, when I select category 1.

My Current SQL follows below:

SELECT * FROM `produto` 
LEFT JOIN `produto_categoria` ON `produto_categoria`.`id`=`produto`.`id_categoria` 
WHERE `produto_categoria`.`id` = '1'`

In this way, I would only look for products of category = 1, but there are variables within this category, as I can return?

I created in SQL FILDDLE: http://www.sqlfiddle.com/#! 9/916cba/1

Answer 1

The query below returns products that are of category 1 or whose products are of a daughter category (1 level) of category 1.

SELECT *
FROM produto p
left JOIN produto_categoria cat 
ON cat.id = p.id_categoria or cat.id_categoria = p.id_categoria;