patternsqlMinor
MySQL parent / child table not showing parent entries
Viewed 0 times
showingparentmysqlchildnottableentries
Problem
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.
I then insert some records:
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:
When I try the following it almost works, but it doesn't return the record for "Cat 2", only it children.
Results:
How can I make it return all 5 records including the "Cat 2" parent category?
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 3When 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_nameResults:
Cat 1 1 NULL NULL
Cat 2 2 4 Subcat 2.1
Cat 2 2 5 Subcat 2.2
Cat 3 3 NULL NULLHow can I make it return all 5 records including the "Cat 2" parent category?
Solution
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 itselfCode Snippets
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 itselfContext
StackExchange Database Administrators Q#158055, answer score: 3
Revisions (0)
No revisions yet.