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'

      

+3


source to share


4 answers


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 

      

+2


source


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 + '%'

      



0


source


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.

0


source


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

      

0


source







All Articles