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.

So I'm guessing there's more than one release with ParentReleaseId equal to zero? — AakashM
– AakashM, Commented Aug 24, 2012 at 13:15
Are you saying that the Releases table is hierarchicial, eg Release X has a predecessor, and then that release has its own predecessor, and so on? So the problem is to find the original release for any later release occurring within the specified dates? — David W
– David W, Commented Aug 24, 2012 at 13:17
Yes there is more than one parent release = 0. David yes you have that right. I am on 2008, Damien — ryan jenkins
– ryan jenkins, Commented Aug 24, 2012 at 13:27
Looks like you got a good push in the right direction with the recursive CTE answer below. Good luck! — David W
– David W, Commented Aug 24, 2012 at 13:59

András Ottó · Accepted Answer · 2012-08-24 13:20:11Z

9

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

answered Aug 24, 2012 at 13:20

András Ottó

7,7351 gold badge30 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

David W Over a year ago

DARN, beat me to it with the CTE. Good job :)

András Ottó Over a year ago

Thanks, I'm using these 'algorithms' a lot in this week :)

András Ottó Over a year ago

Yes, it is not just 4 levels or just 3 or what. It is recursive, searching up. I just wrote a small example.

András Ottó Over a year ago

BTW if somebody would like to test it, here is an sqlFiddle: sqlfiddle.com/#!3/ad1f2/5 (valid on 24.08.2012)

ryan jenkins Over a year ago

Thanks, that will make it a lot easier to apply to my query.

Dumitrescu Bogdan · Accepted Answer · 2012-08-24 13:23:40Z

1

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

answered Aug 24, 2012 at 13:23

Dumitrescu Bogdan

7,2652 gold badges26 silver badges32 bronze badges

2 Comments

David W Over a year ago

I think the anchor member of the CTE will have to deal with the date constraints of the OP's example query, won't it?

Dumitrescu Bogdan Over a year ago

From what I understand the starting value is selected before, and the date constraint will be on that select.

ryan jenkins · Accepted Answer · 2012-08-24 14:30:33Z

Using Andras approach, I edited the final select to directly give me the ID of the root release

WITH cte_Releases
( 
  ReleaseId, 
  ParentReleaseID 
) 
AS( 
  SELECT  
   ReleaseId, 
   ParentReleaseID 
  FROM Releases
  Where ReleaseId = 905

 UNION ALL 

 SELECT  
  R.ReleaseId, 
  R.ParentReleaseID 
 FROM Releases R
 INNER JOIN cte_Releases ON cte_Releases.ParentReleaseID = R.ReleaseId
) 

SELECT max(ReleaseId) as ReleaseId, min(ReleaseId) as RootReleaseId FROM cte_Releases

My problem now is I want to run through all @IDs (905 in that code) and join each record to a result

Collectives™ on Stack Overflow

TSQL - While loop within select?

3 Answers 3

5 Comments

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

2 Comments

Comments

Related