MySQL parent / child table not showing parent entries

Question

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?

Answer 1

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