Pick up all product category levels

Question

Pick up all product category levels

Navigation

#1 by (3 votes)

2

I have a question. I have a table of categories:

CREATE TABLE 'categorias' (
  'id' int(10) UNSIGNED NOT NULL,
  'parent_id' int(10) DEFAULT NULL,
  'lft' int(10) DEFAULT NULL,
  'rght' int(10) DEFAULT NULL,
  'nome' varchar(255) DEFAULT NULL,
  'publicado' int(11) NOT NULL DEFAULT '0',
  'icon' varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And a product table:

CREATE TABLE 'produtos' (
  'id' int(11) NOT NULL,
  'titulo' varchar(255) DEFAULT NULL,
  'data' date DEFAULT NULL,
  'categoria_id' int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Where products.category_id is a foreign key to the category table. The category table has categories and subcategories, where categories.parent_id refers to a category.id to indicate that it is the child of the same.

I need to export the product table with a JOIN taking 3 first levels of the categories, and I do not know which function to use for the best fit.

Result should be: PRODUCT NAME - NAME CATEGORY LEVEL 1 - NAME CATEGORY LEVEL 2 - NAME CATEGORY LEVEL 3 - DATE

Can you help me?

mysql sql

asked by anonymous 10.05.2017 / 20:35

1 answer

Auth laravel error with another model? Regex for capturing fixed strings in HTML and JS codes [closed]

score 3 · Accepted Answer

As I understand it, will the product always be related to the third level? See if the following code helps:

SELECT
p.titulo,
p.id,
n1.nome AS cat1,
n2.nome AS cat2,
n3.nome AS cat3
FROM produtos p
INNER JOIN categorias n3 ON n3.id = p.categoria_id
LEFT OUTER JOIN categorias n2 ON n2.id = n3.parent_id 
LEFT OUTER JOIN categorias n1 ON n1.id = n2.parent_id