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

Question

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

Navigation

#1 by (1 votes)

0

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 is used to assign the parent category.

However I have the following problem:

Select products are carried by the URL of the category that belongs to.

When I do the category category product_category OK.

However when I do 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 ) | product_code ( 1 )

When I select canned , it's OK, but I'd like it when you select it by the Food category. >

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

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 query it like this:

SELECT * FROM view_site_produtos WHERE url = '';

mysql

asked by anonymous 28.02.2018 / 01:13

1 answer

xlrd open files in xls format? WPF C # Doubt - SetFocus in a Text and leave a button disabled

score 1 · Accepted Answer

You need to get all% of% of the children that have that parent to do ids . This is possible through a select , in the example I used the column name, however you can change the subselect to the url column that where should 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');