Início / dba / Perguntas / 158055
Accepted
user32421
Asked: 2016-12-14 08:34:20 +0800 CST

Tabela pai/filho do MySQL não mostrando entradas pai

  • 772

Eu tenho uma tabela na qual as categorias são listadas e cada produto pode pertencer a uma categoria pai ou não. Existe apenas um nível de profundidade possível.

CREATE TABLE `categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `parent` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Em seguida, insiro alguns registros:

INSERT INTO `categories` (`id`, `name`, `parent`) VALUES
(1, 'Cat 1',    NULL),
(2, 'Cat 2',    NULL),
(3, 'Cat 3',    NULL),
(4, 'Subcat 2.1',   2),
(5, 'Subcat 2.2',   2);

Agora eu gostaria de obter uma lista que inclua todas as categorias e subcategorias, ordenadas por categoria principal e com as subcategorias abaixo, assim:

Cat 1
Cat 2
Subcat 2.1
Subcat 2.2
Cat 3

Quando tento o seguinte quase funciona, mas não retorna o registro de "Cat 2", apenas filhos.

SELECT e.name AS parent_name, e.id AS parent_id, r.id AS child_id, r.name AS child_name
     FROM categories e
LEFT JOIN categories r ON e.id = r.parent
    WHERE e.parent IS NULL 
 ORDER BY parent_name, child_name

Resultados:

Cat 1   1   NULL    NULL
Cat 2   2   4   Subcat 2.1
Cat 2   2   5   Subcat 2.2
Cat 3   3   NULL    NULL

Como posso fazer com que ele retorne todos os 5 registros, incluindo a categoria pai "Cat 2"?

mysql hierarchy

1 respostas

  1. Best Answer

    A junção deve estar na direção oposta e sem o WHERE. Pense nisso como começando com qualquer categoria ( r). Todos eles têm um parent(para que parent_name e parent_id apareçam) ou não (para que essas colunas tenham NULL):

    SELECT e.name AS parent_name, 
       e.id AS parent_id, 
       r.id AS child_id, 
       r.name AS child_name
FROM categories r
     LEFT JOIN categories e
     ON e.id = r.parent 
ORDER BY COALESCE(parent_name, child_name),   -- first the parent or itself
         child_name ;                         -- then itself
    • 3

relate perguntas