Dear Pembaca Setia,
Kali ini penulis ingin menge-share contoh SQL Server query untuk logika recursive. Langsung saja, kita lihat contoh berikut;
Kali ini penulis ingin menge-share contoh SQL Server query untuk logika recursive. Langsung saja, kita lihat contoh berikut;
DECLARE @tmp TABLE
(
id int, parent_id int, val_name varchar(50)
id int, parent_id int, val_name varchar(50)
)
INSERT INTO @tmp
(
id, parent_id, val_name
)
(
SELECT 1, 1, 'Super Admin'
SELECT 1, 1, 'Super Admin'
UNION ALL SELECT 2, 1, 'Admin 1'
UNION ALL SELECT 3, 1, 'Admin 2'
UNION ALL SELECT 4, 2, 'User 1'
UNION ALL SELECT 5, 1, 'Admin 3'
)
;WITH cte AS
(
SELECT
SELECT
id, parent_id, val_name
FROM
@tmp a
WHERE
a.id = a.parent_id
UNION ALL
UNION ALL
SELECT
b.id, b.parent_id, b.val_name
FROM
@tmp b
INNER JOIN
cte c
b.id, b.parent_id, b.val_name
FROM
@tmp b
INNER JOIN
cte c
ON b.parent_id = c.id
WHERE
b.id <> b.parent_id
WHERE
b.id <> b.parent_id
)
SELECT *
FROM cte
DELETE @tmp
--===========================================================
-- Dan berikut Hasilnya
--===========================================================
Demikian tutorial hari ini, terima kasih sudah berkenan mampir.
Salam,
Aknutman
Comments
Post a Comment