Select products by PAI category, with products registered in child category

Question

Next, I made the following tables:

CREATE TABLE produto (
  codigo        INT UNSIGNED      NOT NULL AUTO_INCREMENT,
  nome          VARCHAR(128)      NOT NULL,
  url           VARCHAR(255)      NOT NULL,
  PRIMARY KEY (codigo)
);

CREATE TABLE categoria (
  codigo INT UNSIGNED NOT NULL AUTO_INCREMENT,
  nome   VARCHAR(128) NOT NULL,
  url    VARCHAR(255) NOT NULL,
  parent INT UNSIGNED          DEFAULT '0',
  PRIMARY KEY (codigo)
);

CREATE TABLE produto_categoria (
  cod_categoria INT UNSIGNED NOT NULL,
  cod_produto   INT UNSIGNED NOT NULL,
  FOREIGN KEY (cod_categoria) REFERENCES categoria (codigo),
  FOREIGN KEY (cod_produto) REFERENCES produto (codigo)
);

As you can see, the category table has the Parent field, which serves to assign the parent category.

However I have the following problem:

Select products are performed by the URL of the category that belongs.

When I do with the product table category.

However when I select by the parent category, and returned null, because the product does not belong directly to the parent category.

Example:

Product -> Tuna (code 1)

Category -> Food (code 1) -> Canned (code 2| Parent 1)

Product_category -> cod_category (2) | cod_product (1)

When I select by canned, is OK, but I like that when you select for the category Food, the products of the child categories!

I made this view, for select, however it only returns from the category that the product is linked.

CREATE OR REPLACE VIEW view_site_produtos AS
SELECT
  p.codigo, p.nome, c.url,
FROM
  produto p
  LEFT JOIN produto_categoria pc ON p.codigo = pc.cod_produto
  LEFT JOIN categoria c ON pc.cod_categoria = c.codigo;

And I consult as follows:

SELECT * FROM view_site_produtos WHERE url = '';

Answer 1

You need to get them all ids of the children who have that father to make the select. This is possible through a subselect, in the example I used the column name, but you can change the where to the url column select shall not be affected.

SELECT * FROM categoria c
    JOIN produto_categoria pc ON pc.cod_categoria = c.codigo
    JOIN produto p ON p.codigo = pc.cod_produto
WHERE c.codigo IN (select c_filho.codigo from categoria c_pai
                        join categoria c_filho on c_filho.parent = c_pai.codigo
                     where c_pai.nome = 'Comida');