sql – Recursive CTE to find parent records – Education Career Blog

first i must admit that i’m not very familiar with sql server’s recursive CTE’s but i think this is the best approach.

I have a table tabData. Its PK is named idData and there is a self referencing FK fiData.

Schema

So fiData references the parent record and SELECT * FROM tabData WHERE idData=fiData returns all data of the parent. This is simple and fast. But how to get all parents from a given record in the natural order?
Say there is one child(idData=4) with 3 parents (first parent is the record with idData=3):

idData    fiData 
 4          3     
 3          2     
 2          1    
 1          NULL    

I thought recursive CTE is the way to go, but i don’t get along well with its syntax.
So what is the correct way to implement the CTE which returns all parents?

I tried following, but it gives me the wrong result(3,4 instead of 3,2,1):
(To test it i created a temporary table for me and you)

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'tabData_Temp'))
BEGIN
 CREATE TABLE dbo.tabData_Temp(
  idData int NOT NULL,
  fiData int NULL,
   CONSTRAINT PK_tabData_Temp PRIMARY KEY CLUSTERED 
  (
   idData ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
 );

 ALTER TABLE dbo.tabData_Temp  WITH CHECK ADD  CONSTRAINT FK_tabData_Temp FOREIGN KEY(fiData)
 REFERENCES dbo.tabData_Temp (idData);
 ALTER TABLE dbo.tabData_Temp CHECK CONSTRAINT FK_tabData_Temp;

 INSERT INTO dbo.tabData_Temp(idData,fiData)VALUES(1,NULL);
 INSERT INTO dbo.tabData_Temp(idData,fiData)VALUES(2,1);
 INSERT INTO dbo.tabData_Temp(idData,fiData)VALUES(3,2);
 INSERT INTO dbo.tabData_Temp(idData,fiData)VALUES(4,3);
END

/* here comes the (not working) recursive CTE */
Declare @fiData int;
SET @fiData = 3;
WITH PreviousClaims(idData,fiData) 
AS(
     SELECT parent.idData,parent.fiData
     FROM tabData_temp parent
     WHERE parent.idData = @fiData

     UNION ALL

     SELECT child.idData,child.fiData
     FROM tabData_temp child
     INNER JOIN PreviousClaims parent ON parent.idData = child.fiData
)
SELECT idData
FROM PreviousClaims;
/* end of recursive CTE */


DROP TABLE dbo.tabData_Temp;

Thank you in advance.

,

Changing to:

INNER JOIN PreviousClaims parent ON parent.fiData = child.idData

Gave me the results you wanted.

,

You have the join backward.

Change this

INNER JOIN PreviousClaims parent ON parent.idData= child.fiData 

to this

INNER JOIN PreviousClaims parent ON parent.fiData = child.idData

Leave a Comment