By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();CREATE TABLE `label_template_category` (
`label_template_category_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`parent_id` int(11) NOT NULL DEFAULT 0,
`sort_order` int(11) NOT NULL DEFAULT 0,
`date_added` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `label_template_category` (`label_template_category_id`, `name`, `parent_id`, `sort_order`) VALUES
(1, 'Textures', 0, 0),
(3, 'Anniversary', 0, 2),
(4, 'Birthday', 0, 3),
(5, 'Christmas', 0, 4),
(7, 'Father\'s Day', 34, 6),
(9, 'Mother\'s Day', 34, 8),
(10, 'New Baby', 34, 9),
(11, 'New Home', 34, 10),
(13, 'Thank You', 0, 12),
(14, 'Wedding', 0, 13),
(15, 'Business', 0, 0),
(16, 'Valentine\'s Day', 34, 0),
(17, 'Relatable & Funny', 0, 14),
(18, 'St Patricks Day', 0, 0),
(19, 'Halloween', 0, 0),
(20, 'Womens Day', 0, 0),
(21, 'Chinese New Year', 0, 0),
(22, 'New Zealand', 0, 0),
(23, 'Matariki', 0, 0),
(24, 'Easter', 0, 0),
(25, 'Anzac', 0, 0),
(26, 'New Year', 0, 0),
(27, 'Queens Birthday', 0, 0),
(28, 'Retirement', 0, 0),
(29, 'Sports Events/ Teams', 0, 0),
(30, 'In-laws', 0, 0),
(31, 'LGBTQ', 0, 0),
| version() |
|---|
| 8.0.27 |
SELECT
child.label_template_category_id,
child.name,
child.parent_id,
child.sort_order,
CASE WHEN parent.name IS NULL THEN child.name ELSE
CONCAT_WS(' > ',parent.name, child.name) END AS group_name
/*or maybe CONCAT_WS(' > ',parent.name, child.name) END AS group_name */
FROM label_template_category child
LEFT JOIN label_template_category parent
ON (child.parent_id = parent.label_template_category_id);
| label_template_category_id | name | parent_id | sort_order | group_name |
|---|---|---|---|---|
| 1 | Textures | 0 | 0 | Textures |
| 3 | Anniversary | 0 | 2 | Anniversary |
| 4 | Birthday | 0 | 3 | Birthday |
| 5 | Christmas | 0 | 4 | Christmas |
| 7 | Father's Day | 34 | 6 | Celebration Days > Father's Day |
| 9 | Mother's Day | 34 | 8 | Celebration Days > Mother's Day |
| 10 | New Baby | 34 | 9 | Celebration Days > New Baby |
| 11 | New Home | 34 | 10 | Celebration Days > New Home |
| 13 | Thank You | 0 | 12 | Thank You |
| 14 | Wedding | 0 | 13 | Wedding |
| 15 | Business | 0 | 0 | Business |
| 16 | Valentine's Day | 34 | 0 | Celebration Days > Valentine's Day |
| 17 | Relatable & Funny | 0 | 14 | Relatable & Funny |
| 18 | St Patricks Day | 0 | 0 | St Patricks Day |
| 19 | Halloween | 0 | 0 | Halloween |
| 20 | Womens Day | 0 | 0 | Womens Day |
| 21 | Chinese New Year | 0 | 0 | Chinese New Year |
| 22 | New Zealand | 0 | 0 | New Zealand |
| 23 | Matariki | 0 | 0 | Matariki |
| 24 | Easter | 0 | 0 | Easter |
| 25 | Anzac | 0 | 0 | Anzac |
| 26 | New Year | 0 | 0 | New Year |
| 27 | Queens Birthday | 0 | 0 | Queens Birthday |
| 28 | Retirement | 0 | 0 | Retirement |
| 29 | Sports Events/ Teams | 0 | 0 | Sports Events/ Teams |
| 30 | In-laws | 0 | 0 | In-laws |
| 31 | LGBTQ | 0 | 0 | LGBTQ |
| 32 | Condolences | 0 | 0 | Condolences |
| 33 | Wellbeing | 0 | 0 | Wellbeing |
| 34 | Celebration Days | 0 | 0 | Celebration Days |