Self-join request
Is it possible to execute a parent-child query simply by using a non-looping join on a temporary table?
Database example:
menuid name parent url
----------------------------------------------------------
A0000 Master A0000 #
A0001 Rekening A0000 /master/rekening.aspx
A0002 Master Nominal A0001 /master/nominal.aspx
A0003 Master Satuan Other A0001 /master/satuan.aspx
A0004 Master Kondisi A0000 /master/kondisi.aspx
A0005 Master Tujuan A0003 /master/tujuan.aspx
A0006 Master Item A0003 /master/item.aspx
A0007 Master Warehouse A0000 /master/warehouse.aspx
A0008 Master Kapal A0006 /master/kapal.aspx
Desired result if selected uri = '/master/kapal.aspx'
:
menuid name parent url
----------------------------------------------------------
A0000 Master A0000 #
A0001 Rekening A0000 /master/rekening.aspx
A0003 Master Satuan Other A0001 /master/satuan.aspx
A0006 Master Item A0003 /master/item.aspx
A0008 Master Kapal A0006 /master/kapal.aspx
Desired result if selected uri = /master/tujuan.aspx'
:
menuid name parent url
----------------------------------------------------------
A0000 Master A0000 #
A0001 Rekening A0000 /master/rekening.aspx
A0005 Master Tujuan A0003 /master/tujuan.aspx
Request example:
declare @menuid varchar(255) = 'menuid'
declare @parent varchar(255) = 'parent'
declare @temp_parent varchar(255)
declare @i smallint = 0
delete from temp_menu
while (@menuid <> @parent)
begin
if(@i = 0)
begin
insert into temp_menu
select * from menu where uri = '/master/kapal.aspx'
select @menuid = menuid, @parent = parent from menu where uri = '/master/kapal.aspx'
set @i = 1;
end
else
begin
insert into temp_menu
select * from menu where menuid = @parent
select @menuid = menuid, @temp_parent = parent from menu where menuid = @parent
set @parent = @temp_parent;
end
end
select * from temp_menu
Sample with giarorchia:
A0000
|_______________________
| | |
A0001 A0004 A0007
|________
| |
A0002 A0003
|_______
| |
A0005 A0006
|
A0008
UPDATED:
I want to get all lines from the longest branch with nodes parent
up to menuid
and stop if parent
matches menuid
or no menuid
matches parent
.
ADDED WITH SCRIPT AND SAMPLES
IF OBJECT_ID('dbo.menu', 'U') IS NOT NULL
DROP TABLE dbo.menu
GO
IF OBJECT_ID('dbo.temp_menu', 'U') IS NOT NULL
DROP TABLE dbo.temp_menu
GO
IF OBJECTPROPERTY(object_id('dbo.sp_get_parent'), N'IsProcedure') = 1
DROP PROCEDURE dbo.sp_get_parent
GO
create table dbo.menu (
menuid varchar(255)
, name varchar(255)
, parent varchar(255)
, uri varchar(255)
);
insert into dbo.menu (menuid, name, parent, uri)
values ('A0000', 'Master', 'A0000', '#')
, ('A0001', 'Rekening', 'A0000', '/master/rekening.aspx')
, ('A0002', 'Master Nominal', 'A0001', '/master/nominal.aspx')
, ('A0003', 'Master Satuan Other', 'A0001', '/master/satuan.aspx')
, ('A0004', 'Master Kondisi', 'A0000', '/master/kondisi.aspx')
, ('A0005', 'Master Tujuan', 'A0003', '/master/tujuan.aspx')
, ('A0006', 'Master Item', 'A0003', '/master/item.aspx')
, ('A0007', 'Master Warehouse', 'A0000', '/master/warehouse.aspx')
, ('A0008', 'Master Kapal', 'A0006', '/master/kapal.aspx');
create table dbo.temp_menu (
menuid varchar(255)
, name varchar(255)
, parent varchar(255)
, uri varchar(255)
);
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[sp_get_parent]
@uri VARCHAR (255)
AS
declare @menuid varchar(255) = 'menuid'
declare @parent varchar(255) = 'parent'
declare @temp_parent varchar(255)
declare @i smallint = 0
delete from temp_menu
while (@menuid <> @parent)
begin
if(@i = 0)
begin
insert into temp_menu
select * from menu where uri = @uri
select @menuid = menuid, @parent = parent from menu where uri = @uri
set @i = 1;
end
else
begin
insert into temp_menu
select * from menu where menuid = @parent
select @menuid = menuid, @temp_parent = parent from menu where menuid = @parent
set @parent = @temp_parent;
end
end
select * from temp_menu order by menuid asc
GO
For the desired example above, you can try this query:
sp_get_parent '/master/kapal.aspx'
and
sp_get_parent '/master/tujuan.aspx'
source to share
In SQL Server, the answer to every question about how to query hierarchical data uses a recursive common table expression.
In your case, since you want the longest branch, you must add a counting column:
;WITH CTE AS
(
SELECT menuid, name, parent, url, 0 as level
FROM menu WHERE parent = menuid -- Usually, the parent column is simply nullable
UNION ALL
SELECT menu.menuid, menu.name, menu.parent, menu.url, level + 1
FROM menu
INNER JOIN CTE ON menu.parent = CTE.menuid
AND menu.parent <> CTE.parent -- This is why parent column is nullable :-)
)
SELECT TOP 1 *
FROM CTE
ORDER BY Level DESC
This request will deliver you the sheet that is farthest from the top parent.
Update
Based on your comment, I think this is what you are looking for:
;WITH CTERecursion AS
(
SELECT menuid,
name,
parent,
url,
0 as level,
menuid as TopLevelParent
FROM menu WHERE parent = menuid -- Usually, the parent column is simply nullable
UNION ALL
SELECT menu.menuid,
menu.name,
menu.parent,
menu.url,
level + 1,
TopLevelParent
FROM menu
INNER JOIN CTERecursion CTE ON menu.parent = CTE.menuid
AND menu.menuid <> CTE.menuid -- This is why parent column is nullable :-)
), CTELongestPath AS
(
SELECT TOP 1 TopLevelParent
FROM CTERecursion
ORDER BY Level DESC
)
SELECT menuid, name, parent, url
FROM CTERecursion r
INNER JOIN CTELongestPath l ON r.TopLevelParent = r.TopLevelParent
Update # 2
Now that your question has changed, you seem to just want to navigate from sheet to first parent. In this case, your recursive CTE should be something like this:
DECLARE @url varchar(100) = '/master/kapal.aspx';
;WITH CTERecursion AS
(
SELECT menuid,
name,
parent,
url
FROM menu
WHERE url = @url
UNION ALL
SELECT menu.menuid,
menu.name,
menu.parent,
menu.url
FROM menu
INNER JOIN CTERecursion CTE ON menu.menuid = CTE.parent
AND menu.menuid <> CTE.menuid -- This is why parent column is nullable :-)
)
SELECT menuid, name, parent, url
FROM CTERecursion
source to share
drop table if exists dbo.Menu;
create table dbo.Menu (
menuid varchar(100)
, name varchar(100)
, parent varchar(100)
, url varchar(100)
);
insert into dbo.Menu (menuid, name, parent, url)
values ('A0000', 'Master', 'A0000', '#')
, ('A0001', 'Rekening', 'A0000', '/master/rekening.aspx')
, ('A0002', 'Master Nominal', 'A0001', '/master/nominal.aspx')
, ('A0003', 'Master Satuan Other', 'A0001', '/master/satuan.aspx')
, ('A0004', 'Master Kondisi', 'A0000', '/master/kondisi.aspx')
, ('A0005', 'Master Tujuan', 'A0003', '/master/tujuan.aspx')
, ('A0006', 'Master Item', 'A0003', '/master/item.aspx')
, ('A0007', 'Master Warehouse', 'A0000', '/master/warehouse.aspx')
, ('A0008', 'Master Kapal', 'A0006', '/master/kapal.aspx');
with cteMenu as (
select
m.menuid, m.name, m.parent, m.url
, convert(varchar(max), '.' + m.menuid + '.') as Hierarchy
, 0 as Lvl
from dbo.Menu m
where m.menuid = m.parent
union all
select
m.menuid, m.name, m.parent, m.url
, cm.Hierarchy + m.menuid + '.' as Hierarchy
, cm.Lvl + 1 as Lvl
from dbo.Menu m
inner join cteMenu cm on m.parent = cm.menuid
where m.menuid <> m.parent
)
select
cm.menuid, cm.name, cm.parent, cm.url
from (
select
top(1)
cm.*
from cteMenu cm
order by cm.Lvl desc
) t
inner join cteMenu cm on t.Hierarchy like cm.Hierarchy + '%'
source to share
Use a generic table expression (CTE) as follows:
WITH cte_name AS
(
SELECT <base_elements> FROM <table_name> WHERE <root_condition>
UNION ALL
SELECT <child_elements>
FROM cte_name
JOIN <table_name> ON cte_name.id = <table_name>.parentid
)
SELECT DISTINCT * FROM cte_name
What this means is, it will select all elements that are root elements. In your case, it would be something like menuid = 'A0000'
.
Then it concatenates all those elements back into itself and rejoins the original table if it finds matches for parentId (child) -Id in your case if the condition is menu.parent = cte.menuid
.
And then it selects everything other than the recursive query netting you all the paths back. If you want to get the maximum depth you will need to add a constant value for root select like (0) and then increase it afterwards every time you combine all. Then you can choose max(nesting_level)
from the final individual request.
In your case something like this might work
WITH cteMenu AS
(
SELECT menuid, name, parent, url, 0 as nesting_level FROM menu WHERE menuid = 'A0000'
UNION ALL
SELECT menu.menuid, menu.name, menu.parent, menu.url, nesting_level + 1
FROM menu
JOIN cteMenu ON menu.parent = cteMenu.menuid
WHERE menu.id <> 'A0000'
)
SELECT DISTINCT * FROM cteMenu WHERE nesting_level = (SELECT MAX(nesting_level) FROM cteMenu)
/ update: added a WHERE clause WHERE menu.id <> 'A0000'
to the inner select, this removes the root element from the selection and stops infinite recursion.
source to share
declare @uri nvarchar(256) = '/master/kapal.aspx'
; with cte as (
select MenuId, Name, Parent, Url
, 1 reverseOrder
from MyTable
where url = @uri
union all
select b.MenuId, b.Name, b.Parent, b.Url
, a.reverseOrder + 1
from cte a
inner join MyTable b
where b.MenuId = a.Parent
and b.MenuId = a.MenuId --don't repeat ourselves after reaching the root
)
select MenuId, Name, Parent, Url
from cte
ordre by reverseOrder desc
Update
For the second part of your question (like the longest thread) try:
; with cte as (
select MenuId, Name, Parent, Url
, 1 branchLength
, cast(MenuId as nvarchar(max)) branchPath
from MyTable
where Parent = menuid --i.e. top level / root elements
union all
select b.MenuId, b.Name, b.Parent, b.Url
, a.branchLength + 1
, a.branchPath + '\' + cast(b.MenuId as nvarchar(max)) branchPath
from cte a
inner join MyTable b
where b.Parent = a.MenuId --i.e. branch of previous result
and b.MenuId != a.MenuId --ensure we don't repeat ourselves
)
select a.MenuId, a.Name, a.Parent, a.Url
from cte a
inner join (
select top 1 branchPath
from cte
order by branchLength Desc
) b
on b.branchPath like a.branchPath + '%' --gives us an easy way to traverse back up the tree, without recording every possible paths' inheritance / by using a directory structure
ordre by a.branchLength desc
source to share