declare @test table
(
aid int,
pid int
)
insert @test
values(100, null),
(200, 100),
(300, 200),
(400, 300),
(500, 400),
(600, 500)

-- table 2

declare @test1 table
(
aid int,
aname varchar(100)
)
insert @test1
values(100, 'abc'),
(200, 'abcd')

declare @ID int
set @ID = 500
;With CTE
AS
(
SELECT t.aid,t.pid,CAST(t.aid AS varchar(max)) AS [Path]
FROM @test t
where t.aid = @ID

union all

select t.aid,t.pid,c.Path + ',' + CAST(t.aid AS varchar(max))
from CTE c
inner join @test t
ON t.aid = c.pid
)


SELECT LEFT(Path,COALESCE(NULLIF(CHARINDEX(',',Path),0),LEN(Path)+1)-1) AS aid,
aname
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY LEFT(Path,COALESCE(NULLIF(CHARINDEX(',',Path),0),LEN(Path)+1)-1) ORDER BY LEN(Path)) AS Rn
FROM CTE c
CROSS APPLY (SELECT aname
            FROM @test1
			WHERE aid = c.aid
			)c1
)t
WHERE Rn=1


output
----------------------------------------
aid	aname
500	abcd

-- **********************************************************************
-- Initialization of sample data
-- **********************************************************************

DECLARE @test TABLE (aid INT,pid INT);
DECLARE @test1 TABLE (aid INT, aname VARCHAR(50));

INSERT @test VALUES(100,NULL);
INSERT @test VALUES(200,100);
INSERT @test VALUES(300,200);
INSERT @test VALUES(400,300);
INSERT @test VALUES(500,400);
INSERT @test VALUES(600,500);

INSERT @test1 VALUES(100,'abc');
INSERT @test1 VALUES(200,'abcd');



-- **********************************************************************
-- CTE Example
-- **********************************************************************

DECLARE @param_example_aid INT;
SET @param_example_aid = 500;

/* -- cteHierarchy
The Hierarchy CTE returns the all nodes below a root. The "Root" is the AID of
the original node. The root is provided so that your table can accomodate 
multiple hierarchies. An incrementing "Level" identifier is included as well
that indicates how far from the root a given node is.  This is helpful when
identifying 

Results:
Root LEVEL aid  pid
100  0     100  NULL  -- Our root node, notice the aid matches the "root" column
100  1     200  100
100  2     300  200
100  3     400  300
100  4     500  400
100  5     600  500

*/
WITH cteHierarchy
AS
(
-- Root / Anchor Level - Only entries with NULL parent.  Starts the recursion.
SELECT t.aid Root, 0 AS LEVEL, t.aid, t.pid
FROM @test AS t
WHERE t.pid IS NULL 
UNION ALL
-- The recursive query gets children of all current rows for each iteration
SELECT  c.root, c.Level+1 AS LEVEL, t.aid, t.pid
FROM @test AS t
JOIN cteHierarchy AS c  -- referring back to the cte causes recursion
ON t.pid = c.aid
)

/* -- cteReverseOrderedHierarchyPath
The Reverse Ordered Hierarchy Path CTE is a "chained cte" that uses cteHierarchy
to return all nodes in a hierarchy path starting from a given "node": @param_example_aid.
We identify our node as "c1" and get the entire hierarchy as "c2" via the "root".
This is necessary in case there are multiple hierarchies.   

Results of intermediary stage - left join to @test1:

C1 - Original Node      C2 - Hierarchy of C1    @test1
---------------------   ---------------------   -----------
Root  LEVEL  aid  pid | Root  LEVEL aid  pid  | aid   aname
----  -----  ---  --- | ----  ----- ---  ---  | ---   -----
100   4      500  400 | 100   0     100  NULL | 100   abc
100   4      500  400 | 100   1     200  100  | 200   abcd
100   4      500  400 | 100   2     300  200  | NULL  NULL
100   4      500  400 | 100   3     400  300  | NULL  NULL

-- other rows excluded from the resultset with "and c2.Level <= c1.Level"
100   4      500  400 | 100   4     500  400  | NULL  NULL
100   4      500  400 | 100   5     600  500  | NULL  NULL


Once we have the hierarchy, we inner join it to @test1, which reduces the 
resultset to only entries that have a name ("aname").  Then, we apply the
ROW_NUMBER() windowing function to "reverse order" the entries by level,
so that the first entry encountered UP the hierarchy will always have a
Row Number of 1.

Results of final stage - inner join to @test1:

C1 - Original Node      C2 - Hierarchy of C1    @test1        Reverse Order
---------------------   ---------------------   -----------   -------------
Root  LEVEL  aid  pid | Root  LEVEL aid  pid  | aid   aname | rn
----  -----  ---  --- | ----  ----- ---  ---  | ---   ----- | -------------
100   4      500  400 | 100   0     100  NULL | 100   abc   | 2
100   4      500  400 | 100   1     200  100  | 200   abcd  | 1
*/

, cteReverseOrderedHierarchyPath
AS
(
SELECT c2.aid, n.aname, ROW_NUMBER() OVER(PARTITION BY c2.Root ORDER BY c2.Level DESC) AS Rn
FROM cteHierarchy AS c1
JOIN cteHierarchy AS c2 ON c1.Root = c2.Root
JOIN @test1 AS n ON c2.aid = n.aid
WHERE c1.aid = @param_example_aid 
AND c2.Level <= c1.Level
)
/* 
Finally, we select the entry that has a rn = 1 and get our "first encountered"
aname. Note that aid of the row found will be returned.  In your example, you
simply returned the @param_example_aid.  Easy enough to change if needed, but any
code passing in @param_example_aid should already have it.

Results:
aid  aname
200  abcd
*/
SELECT z.aid, z.aname
FROM cteReverseOrderedHierarchyPath AS z
WHERE z.rn = 1

-- **********************************************************************
-- Initialization of sample data
-- **********************************************************************

DECLARE @test TABLE (aid INT,pid INT);
DECLARE @test1 TABLE (aid INT, aname VARCHAR(50));

INSERT @test VALUES(100,NULL);
INSERT @test VALUES(200,100);
INSERT @test VALUES(300,200);
INSERT @test VALUES(400,300);
INSERT @test VALUES(500,400);
INSERT @test VALUES(600,500);

INSERT @test1 VALUES(100,'abc');
INSERT @test1 VALUES(200,'abcd');



-- **********************************************************************
-- CTE Example
-- **********************************************************************

DECLARE @param_example_aid INT;
SET @param_example_aid = 500;

/* -- cteHierarchy
The Hierarchy CTE returns the all nodes above the param. The "Root" is the AID of
the original node. An incrementing "Level" identifier is included as well
that indicates how far from the root a given node is.  

Results:
Root LEVEL	aid	pid
500  0      500	400
500  1      400	300
500  2      300	200
500  3      200	100
500  4      100	NULL

*/
WITH cteHierarchy
AS
(
-- Root / Anchor Level - start with @param_example_aid.
SELECT t.aid Root, 0 AS LEVEL, t.aid, t.pid
FROM @test AS t
WHERE t.aid = @param_example_aid
UNION ALL
-- The recursive query gets parents of anchor 
SELECT  c.root, c.Level+1 AS LEVEL, t.aid, t.pid
FROM @test AS t
JOIN cteHierarchy AS c  -- referring back to the cte causes recursion
ON t.aid = c.pid
)

/* -- cteOrderedHierarchyPath
The Reverse Ordered Hierarchy Path CTE is a "chained cte" that uses cteHierarchy
to return all nodes in a hierarchy path starting from a given "node": @param_example_aid.
We identify our node as "c1" and get the entire hierarchy as "c2" via the "root".
This is necessary in case there are multiple hierarchies.   

*/
, cteOrderedHierarchyPath
AS
(
SELECT c1.aid, n.aname, ROW_NUMBER() OVER(PARTITION BY c1.Root ORDER BY c1.Level) AS Rn
FROM cteHierarchy AS c1
JOIN @test1 AS n ON c1.aid = n.aid
)
/* 
Finally, we select the entry that has a rn = 1 and get our "first encountered"
aname. Note that aid of the row found will be returned.  In your example, you
simply returned the @param_example_aid.  Easy enough to change if needed, but any
code passing in @param_example_aid should already have it.
*/
SELECT z.aid, z.aname
FROM cteOrderedHierarchyPath AS z
WHERE z.rn = 1

DECLARE @aid INT = 500;

WITH CTE AS
(
   SELECT @aid AS aid, (SELECT T1.aname FROM test1 AS T1 WHERE T1.aid = @aid) AS aname

    UNION ALL

   SELECT T.pid, (SELECT T1.aname FROM test1 AS T1 WHERE T1.aid = T.pid)
     FROM CTE AS P
          INNER JOIN
          test AS T
          ON T.aid = P.aid
    WHERE P.aname IS NULL
)
SELECT @aid AS aid, T.aname
  FROM CTE AS T
 WHERE T.aname IS NOT NULL;

quote:

---

Originally posted by lazerath

No. Post code that can be replayed, like my own, else you arent even playing.

---

USE tempdb;

IF OBJECT_ID(N'tempdb..test', N'U') IS NULL
   SELECT T.aid, T.pid
     INTO tempdb..test
     FROM (VALUES(100, NULL), (200, 100), (300, 200), (400, 300), (500, 400), (600, 500)) AS T(aid, pid);

IF OBJECT_ID(N'tempdb..test1', N'U') IS NULL
   SELECT T.aid, T.aname
     INTO tempdb..test1
     FROM (VALUES(100, 'abc'), (200, 'abcd')) AS T(aid, aname);

DECLARE @aid INT = 500;

WITH CTE AS
(
   SELECT @aid AS aid, (SELECT T1.aname FROM test1 AS T1 WHERE T1.aid = @aid) AS aname

    UNION ALL

   SELECT T.pid, (SELECT T1.aname FROM test1 AS T1 WHERE T1.aid = T.pid)
     FROM CTE AS P
          INNER JOIN
          test AS T
          ON T.aid = P.aid
    WHERE P.aname IS NULL
)
SELECT @aid AS aid, T.aname
  FROM CTE AS T
 WHERE T.aname IS NOT NULL;

quote:

---

Originally posted by lazerath

visakh16 has an interesting solution, but it is mixing derived tables and ctes, which is a design pattern I try to avoid. Once you become familiar with CTEs, they are easier to read and maintain because it's a top down "flow" vs the nesting behavior in derived tables. There is no performance difference between the two.

There is a problem in this solution you should be aware of. Currently, it relies on the length of "PATH", which in your current sample data is fixed. However, if you begin having AIDs with differing lengths (such as 1, 10, 100, 1000, 10000), the PATH length may not correspond to level depending on the hierarchy structure. I try to avoid putting any dependency on externalalities such as surrogate key length and sequence so as to avoid inflexibility in my solutions and hard to diagnose bugs that crop up after data changes.

Additionally, the CROSS APPLY is superfluous. I'd simply use a JOIN. The only reason it exists is so that the solution can use SELECT * without creating ambiguous column names.

---

quote:

---

Originally posted by lazerath



@visakh16: While using CROSS APPLY within the solution was technically possible, my point is that it over complicates the solution. In T-SQL, there are often many ways to do achieve the same result, so simplicity is a virtue. In this case, a simple JOIN could be used in it's place -- eliminating a SELECT * which is not a best practice -- and it would make the solution easier to understand. This, in turn, improves the effectiveness of your assistance and helps the poster on their road to mastery.

In general, I also see CROSS APPLY misunderstood a lot, so the more it can be used in situations where it is actually the best option, the better we can correct misunderstandings and improve the general knowledge of the community. As such, I try to avoid using it unless it actually is the best option.

I will grant you that this is more of a philosophical point, but at our level of the craft I consider it worth bringing up.


@malpashaa: Thanks for submitting your solution. It is definitely simpler and faster. It also gave me something to think about and showed me another way of working with recursive loops.

Secondly, my apologies for being curt with you. I could have worded my reply far better.


@ssunny: Glad to be of assistance!

---