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 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 = '';
You saved me, thank you very much!
– André Felipe