SQL Server 2008r2 Recursion with CTE

[wbodger]

I have used CTEs, but never for what I have heard they are good at and now I have a circumstance where I need to find a value recursively. I have a table of specimens, both parent and child (and grandchild and great grandchild etc.) from which I need to find some relationships. For instance, each specimen (specimenid) has a specimen type (specimentypeid) and a parentid (which is blank if it is the first parent). So, specimen A can be processed and specimen x2 created, then specimen x2 is processed and specimen d8 is created. For another specimen B it may be processed and t5 is created. Let's say that I need to take every specimentypeid=12 and find every one with a parent (either immediate or final) with specimentypeid=2. How do I build a recursive CTE that will search through the entire table and start with one specimen type then search up the chain until it hits the top (parentid is null) for the specimen chain and then restrict by speciemntypeid?

Thanks,
Willie

 

[wbodger]

As a start, is this close?
WITH CTE_SPECIMEN_HEIRARCHY AS
(
SELECT E.SpecimenID, E.ParentSpecimenID AS Parent, E.SpecimenStoredID
FROM db_LIMS_EDRN_STS.dbo.tblSpecimenBank316 E

UNION ALL

SELECT E1.SpecimenID, E1.ParentSpecimenID AS Parent, E1.SpecimenStoredID
FROM CTE_SPECIMEN_HEIRARCHY
JOIN db_LIMS_EDRN_STS.dbo.tblSpecimenBank316 E1
ON E1.ParentSpecimenID = CTE_SPECIMEN_HEIRARCHY.SpecimenID

)

SELECT * FROM CTE_SPECIMEN_HEIRARCHY
where Parent is null
and SpecimenStoredID=2

 

[bborissov]

Could you, please, post some example data and what you want as a result from it?

Borislav Borissov
VFP9 SP2, SQL Server

 

[wbodger]

Here is some data:

SpecimenID	PptID	ParentSpecimenID	SpecimenStoredID
3201285790	66232000645	NULL	        80
3201285807	66232000645	3201285790	81
3201285825 	66232000645	3201285807	83
3201726813	66232000645	3201285790	84
3201726822	66232000645	3201285790	84
3201726831 	66232000645	3201726822	83
3201726840	66232000645	3201285790	84
3201262859	67632000652	NULL	        80
3201262868	67632000652	3201262859	81
3201262886	67632000652	3201262868	83
3201726617	67632000652	3201262859	84
3201726626 	67632000652	3201262859	83

Let's say I want every specimen with specimenstoredid=83 whose orginal parent specimenstoredid=80. In this case I would want my result to include 3201285825 and 3201726831, but not 3201262886 or 3201726626. There may be four or five layers up to find the original and the only way to know for sure you are at the top is parentspecimenid is null.