MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `test_pages` (

`id` char(36) COLLATE utf8mb4_general_ci NOT NULL,

`version_id` int unsigned NOT NULL,

`parent_id` char(36) COLLATE utf8mb4_general_ci DEFAULT NULL,

`default_page_id` char(36) COLLATE utf8mb4_general_ci DEFAULT NULL,

`type` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,

`title` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,

`created` datetime DEFAULT NULL,

`modified` datetime DEFAULT NULL,

PRIMARY KEY (`id`,`version_id`),

KEY `version_id` (`version_id`),

KEY `parent_id` (`parent_id`,`version_id`),

CONSTRAINT `pages_fk_1` FOREIGN KEY (`parent_id`, `version_id`) REFERENCES `test_pages` (`id`, `version_id`) ON DELETE RESTRICT ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------

-- Records of `test_pages`

-- ----------------------------

BEGIN;

INSERT INTO `test_pages` VALUES

('abc1212312efff', '2', null, null, 'root', 'Root Page', null, null),

('ejejejejejej', '2', 'abc1212312efff', null, 'container', 'Child Page 1', '2022-02-03 23:26:01', null),

('jf2039jf932032f', '2', 'abc1212312efff', null, 'container', 'Child Page 2', '2022-02-23 23:25:41', null),

('fefeee9f9e9fee', '2', null, null, 'root', 'Root Page With No Children', null, null),

('7eajiefaeiaeaeleailje', '2', null, null, 'root', 'Root Page A', null, null),

('8efjaliejfaelfjael', '2', '7eajiefaeiaeaeleailje', null, 'container', 'Child Page Again 1', '2022-02-04 23:26:01', null),

('jklfjealkjlfajfl', '2', '7eajiefaeiaeaeleailje', null, 'container', 'Child Page Again 2', '2022-02-07 23:25:41', null);

COMMIT;

SELECT

test_pages.*

FROM

test_pages

WHERE

test_pages.default_page_id IS NULL

AND test_pages.type = "root";

id	version_id	parent_id	default_page_id	type	title	created	modified
7eajiefaeiaeaeleailje	2	null	null	root	Root Page A	null	null
abc1212312efff	2	null	null	root	Root Page	null	null
fefeee9f9e9fee	2	null	null	root	Root Page With No Children	null	null