Pick all levels of product categories

Question

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.categoria_id is a foreign key from the table of categories. The category table has categories and subcategories, and the.parent_id category references a.id category to indicate that it is a daughter of the same.

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

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

Can you help me?

Answer 1

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

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