MySQL parent / child table not showing parent entries

hierarchyMySQL

I have a table in which categories are listed, and each product can belong to a parent category or not. There is only one level of possible depth. 

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`)
);

I then insert some records:

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);

Now I'd like to get a list that includes all the categories and subcategories, ordered by main category and with the subcategories underneath, like so:

Cat 1
Cat 2
Subcat 2.1
Subcat 2.2
Cat 3

When I try the following it almost works, but it doesn't return the record for "Cat 2", only it children. 

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

Results:

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

How can I make it return all 5 records including the "Cat 2" parent category?

Best Answer

The join should be on the opposite direction and without the WHERE. Think of this as starting with any category (r). All of them either have a parent (so the parent_name and parent_id will appear) or not (so those columns will have 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
Related Question