DSQL

Требуется из древовидной таблицы отобрать все значения родительского ключа, а также значения всех подчиненных элементов с учетом «вложения» элементов в родительский ключ.

Наиболее иллюстративным является пример, предложенный Таблоидом на очередной вопрос на формуе SQL.RU.

Скрипт для создания тестовых данных

recreate global TEMPORARY TABLE tmp$dynasty(id INT NOT NULL, pid INT, name VARCHAR(40))
ON commit preserve ROWS;
-- ID`шники намеренно введены с нарушениями последовательности натурального ряда
INSERT INTO tmp$dynasty(id,pid,name)VALUES(777,NULL,'Саваоф');
INSERT INTO tmp$dynasty(id,pid,name)VALUES(159,777,'Адам');
INSERT INTO tmp$dynasty(id,pid,name)VALUES(77,159,'Каин');
INSERT INTO tmp$dynasty(id,pid,name)VALUES(4,159,'Авель');
INSERT INTO tmp$dynasty(id,pid,name)VALUES(351,159,'Сиф');
INSERT INTO tmp$dynasty(id,pid,name)VALUES(373,77,'Енох');
 
INSERT INTO tmp$dynasty(id,pid,name)VALUES(11,373,'Ирад');
INSERT INTO tmp$dynasty(id,pid,name)VALUES(12,11,'Мехиаэль');
 
INSERT INTO tmp$dynasty(id,pid,name)VALUES(109,351,'Енос');
INSERT INTO tmp$dynasty(id,pid,name)VALUES(14,109,'Каинан');
INSERT INTO tmp$dynasty(id,pid,name)VALUES(515,14,'Малелеил');
INSERT INTO tmp$dynasty(id,pid,name)VALUES(16,515,'Иаред');
INSERT INTO tmp$dynasty(id,pid,name)VALUES(17,16,'Ламех');
INSERT INTO tmp$dynasty(id,pid,name)VALUES(203,17,'Ной');
INSERT INTO tmp$dynasty(id,pid,name)VALUES(19,203,'Сим');
INSERT INTO tmp$dynasty(id,pid,name)VALUES(20,203,'Хам');
INSERT INTO tmp$dynasty(id,pid,name)VALUES(21,203,'Иафет');
 
INSERT INTO tmp$dynasty(id,pid,name)VALUES(421,19,'Арфаксад');
INSERT INTO tmp$dynasty(id,pid,name)VALUES(23,421,'Елам');
INSERT INTO tmp$dynasty(id,pid,name)VALUES(24,421,'Ассур');
INSERT INTO tmp$dynasty(id,pid,name)VALUES(25,421,'Луд');
INSERT INTO tmp$dynasty(id,pid,name)VALUES(69,421,'Арам');
 
INSERT INTO tmp$dynasty(id,pid,name)VALUES(327,19,'Сала');
INSERT INTO tmp$dynasty(id,pid,name)VALUES(28,327,'Евер');
INSERT INTO tmp$dynasty(id,pid,name)VALUES(29,28,'Фалек');
 
INSERT INTO tmp$dynasty(id,pid,name)VALUES(30,28,'Иоктан');
 
INSERT INTO tmp$dynasty(id,pid,name)VALUES(31,69,'Уц');
INSERT INTO tmp$dynasty(id,pid,name)VALUES(32,69,'Хул');
INSERT INTO tmp$dynasty(id,pid,name)VALUES(33,69,'Гефер');
INSERT INTO tmp$dynasty(id,pid,name)VALUES(34,69,'Маш');
 
INSERT INTO tmp$dynasty(id,pid,name)VALUES(55,29,'Рагав');
INSERT INTO tmp$dynasty(id,pid,name)VALUES(536,55,'Серух');
INSERT INTO tmp$dynasty(id,pid,name)VALUES(37,536,'Нахор');
INSERT INTO tmp$dynasty(id,pid,name)VALUES(38,37,'Фарра');
commit;

Скрипт запроса к базе данных

WITH recursive c AS (SELECT NULL id FROM FROM rdb$database)
,r AS(
 SELECT
   t.pid parent_id
   ,t.id
   ,t.name
   ,1 lev
   ,CAST(lpad(CAST(t.id AS VARCHAR(12)),12,'0')||'#' AS VARCHAR(2000)) fpath
   ,CAST(t.name AS VARCHAR(2000)) fname
 FROM tmp$dynasty t
 LEFT JOIN tmp$dynasty p ON t.pid=p.id
 WHERE t.pid IS NULL
 UNION ALL
 SELECT
   t.pid
   ,t.id
   ,t.name
   ,r.lev+1
   ,r.fpath || lpad(CAST(t.id AS VARCHAR(12)),12,'0')||'#'
   ,lpad('',3*(r.lev+1),'.') || t.name
   FROM tmp$dynasty t
   JOIN r ON t.pid=r.id
)
SELECT c.id_from,r.id,r.fname --,r.fpath
FROM r, c
WHERE c.id_from IS NULL OR r.fpath LIKE '%'||lpad(CAST(c.id_from AS VARCHAR(12)),12,'0')||'#%'
ORDER BY fpath

Результат выполнения скрипта запроса

ID_FROM	ID	FNAME
	777	Саваоф
	159	......Адам
	4	.........Авель
	77	.........Каин
	373	............Енох
	11	...............Ирад
	12	..................Мехиаэль
	351	.........Сиф
	109	............Енос
	14	...............Каинан
	515	..................Малелеил
	16	.....................Иаред
	17	........................Ламех
	203	...........................Ной
	19	..............................Сим
	327	.................................Сала
	28	....................................Евер
	29	.......................................Фалек
	55	..........................................Рагав
	536	.............................................Серух
	37	................................................Нахор
	38	...................................................Фарра
	30	.......................................Иоктан
	421	.................................Арфаксад
	23	....................................Елам
	24	....................................Ассур
	25	....................................Луд
	69	....................................Арам
	31	.......................................Уц
	32	.......................................Хул
	33	.......................................Гефер
	34	.......................................Маш
	20	..............................Хам
	21	..............................Иафет

http://www.sql.ru/forum/actualthread.aspx?bid=2&tid=873434