I have a table with this schema:
CREATE TABLE [td].[MyTable] ( [ID] [int] NOT NULL ,[FatherID] [int] NULL )
(Note: I have excluded all the columns not relevant to the discussion)
I receive an
[ID] as input, and I need to collect the relative record and all of its fathers in one single output.
Every record has the id of the Father stored in the
[FatherID] column. There is only one root element and it is recognized when we found the
[FatherID] = 0.
This is the current full working query:
IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL DROP TABLE #MyTempTable DECLARE @ID CHAR(11) SET @ID = 13192 SELECT * INTO #MyTempTable FROM [MyTable] WHERE [ID] = @ID SELECT @ID = [FatherID] FROM #MyTempTable WHERE [ID] = @ID WHILE @ID <> 0 BEGIN INSERT INTO #MyTempTable SELECT * FROM [MyTable] WHERE [ID] = @ID SELECT @ID = [FatherID] FROM #MyTempTable WHERE [ID] = @ID END SELECT * FROM #MyTempTable ORDER BY [ID]
The perfomance are fine, but I want improve the readability. Any suggestions?