SQL Query for Tree Table

Sql query for tree table

Expanding on a_horse_with_no_name's answer, this show how to use SQL Server's implementation of recursive CTE (recursive single-record cross apply) in combination with row_number() to produce the exact output in the question.

declare @t table(id int,parentId int,name varchar(20))
insert @t select 1,  0        ,'Category1'
insert @t select 2,  0,        'Category2'
insert @t select 3,  1,        'Category3'
insert @t select 4 , 2,        'Category4'
insert @t select 5 , 1,        'Category5'
insert @t select 6 , 2,        'Category6'
insert @t select 7 , 3,        'Category7'
;

WITH tree (id, parentid, level, name, rn) as 
(
   SELECT id, parentid, 0 as level, name,
       convert(varchar(max),right(row_number() over (order by id),10)) rn
   FROM @t
   WHERE parentid = 0

   UNION ALL

   SELECT c2.id, c2.parentid, tree.level + 1, c2.name,
       rn + '/' + convert(varchar(max),right(row_number() over (order by tree.id),10))
   FROM @t c2 
     INNER JOIN tree ON tree.id = c2.parentid
)
SELECT *
FROM tree
ORDER BY cast('/' + RN + '/' as hierarchyid)

To be honest, using the IDs themselves to produce the tree "path" would work, since we are ordering directly by id, but I thought I'd slip in the row_number() function.

Q:Sql query for tree table

Your probably looking for an iterative cte. Quite well documented if you search. See below for a quick example. The technique is to use a common table expression that references itself. Be careful of loops in your data though.

DECLARE @SearchTerm NVARCHAR(10) = N'car';
DECLARE @Table AS TABLE ( id INT, product1 NVARCHAR(10), product2 NVARCHAR(10));

INSERT INTO @Table
VALUES ( 1, 'car', 'Tire' )
,      ( 2, 'car', 'meter' )
,      ( 3, 'Tire', 'tireA' )
,      ( 4, 'Tire', 'tireB' )
,      ( 5, 'meter', 'meterA' )
,      ( 6, 'meter', 'meterB' )
,      ( 7, 'meter', 'meterC' )
,      ( 7, 'meterC', 'meterCC' );

WITH cte AS ( SELECT    1           AS level
              ,         T1.id
              ,         T1.product1 Parent
              ,         T1.product2 Child
                FROM    @Table T1
               WHERE T1.product1 = @SearchTerm
              UNION ALL
              SELECT    cte.level + 1
              ,         T2.*
                FROM    @Table T2
                JOIN cte ON cte.Child = T2.product1 )
SELECT  *
  FROM  cte;

SQL Query of 2 Tables forming a category tree

You need left join with base table as category_name.

Select cn.categoryid, cn.cat_name,
       Cnp.cat_name as parent_category
  From category_name cn
  Left join category c on c.categoryid = cn.categoryid
  Left join category_name cnp on cnp.categoryid = c.parentid;

How to get the third level of a tree in SQL

You need to do the same join with different aliases to get the last generation
The other option is to make recursive CTE.

SELECT 
    G4.*
FROM relations GR1
    INNER JOIN family G1 ON GR1.r_parent = G1.f_id
    INNER JOIN family G2 on GR1.r_child = G2.f_id
    INNER JOIN relations GR2 ON GR2.r_parent = G2.f_id
    INNER JOIN family G3 ON GR2.r_child = G3.f_id
    INNER JOIN relations GR3 ON GR3.r_parent = G3.f_id
    INNER JOIN family G4 ON GR3.r_child = G4.f_id

The script used is:

CREATE TABLE family(f_id int ,f_name nvarchar (200))
INSERT INTO family 
VALUES 
  (1, 'Name Surname 1')
, (2, 'Name Surname 2')
, (3, 'Name Surname 3')
, (4, 'Name Surname 4')
, (5, 'Name Surname 5')
, (6, 'Name Surname 6')
, (7, 'Name Surname 7')
, (8, 'Name Surname 8')
, (9, 'Name Surname 9')
, (10, 'Name Surname 10')

CREATE TABLE relations (r_parent int, r_child int)
INSERT INTO relations VALUES (1, 2), (1, 3), (2,4), (2,5), (3,6), (3,7), (3,8), (7,9), (8,10)

SELECT 
    G4.*
FROM relations GR1
    INNER JOIN family G1 ON GR1.r_parent = G1.f_id
    INNER JOIN family G2 on GR1.r_child = G2.f_id
    INNER JOIN relations GR2 ON GR2.r_parent = G2.f_id
    INNER JOIN family G3 ON GR2.r_child = G3.f_id
    INNER JOIN relations GR3 ON GR3.r_parent = G3.f_id
    INNER JOIN family G4 ON GR3.r_child = G4.f_id

DROP TABLE family
DROP TABLE relations

Flatten tree structure represented in SQL

Have a look at Recursive Common Table Expressions (CTEs). They should be able to accomplish exactly what you need.

Have a look at Example D on the SQL Docs page.

Basically what you'd do in your case is:

• In the "anchor member" of the CTE, select all top-level items
• In the "recursive member" of the CTE, join all of the nested children to the top-level item

Recursive CTEs are not really trivial to understand, so be sure to read the docs carefully.

---

Related Topics