TSQL - While loop within select?

Question

In SQL server

Ok, so I'm working with a database table in which rows can have parent rows, which can then have parent rows of their own. I need to select the root 'row'. I don't know the best way to do this.

There is a field called ParentId, which links the row to the row with that ID. When the ParentId = 0, it is the root row.

This is my query now:

SELECT Releases.Name,WorkLog.WorkLogId 

FROM WorkLog,Releases
WHERE
Releases.ReleaseId = WorkLog.ReleaseId
and WorkLogDateTime >= @StartDate
and WorkLogDateTime <= @end

---

I don't really need the Release Name of the child releases, I want only the root Release Name, so I want to select the result of a While loop like this:

WHILE (ParentReleaseId != 0)
BEGIN
@ReleaseId = ParentReleaseId
END

Select Release.Name
where Release.RealeaseId = @ReleaseId

I know that syntax is horrible, but hopefully I'm giving you an idea of what I'm trying to acheive.

Answer 1

Here is an example, which could be usefull:

This query is getting a lower element of a tree, and searching up to the parent of parents. Like I have 4 level in my table -> category 7->5, 5->3, 3-> 1. If i give it to the 5 it will find the 1, because this is the top level of the three.

(Changing the last select you can have all of the parents up on the way.)

DECLARE @ID int

SET @ID = 5;

WITH CTE_Table_1
(
  ID,
  Name,
  ParentID
)
AS(
  SELECT 
   ID,
   Name,
   ParentID
  FROM Table_1
  WHERE ID = @ID

 UNION ALL

 SELECT 
  T.ID,
  T.Name,
  T.ParentID
 FROM Table_1 T
 INNER JOIN CTE_Table_1 ON CTE_Table_1.ParentID = T.ID
)

SELECT * FROM CTE_Table_1 WHERE ParentID = 0

Answer 2

something like this

with cte as
(
  select id,parent_id from t where t.id=@myStartingValue
  union all
  select t.id,t.parent_id
  from cte
  join t on cte.parent_id = t.id where cte.parent_id<>0
 )
select * 
from cte
join t on cte.id=t.id where cte.parent_id = 0

and with fiddle : http://sqlfiddle.com/#!3/a5fa1/1/0