SQl query required for the below Scenario

Question

Here for part ‘CF061W’ finum is 25, I will select records whose fparinum value is 25 now I will get these parts FA061W, HRD20600 and SD1201. Now again I will select records whose fparinum value is finumber of above retrieved parts FA061W, HRD20600 and SD1201 and so on. This should continue till the highest level (flevel), for the above table it is up to level 4.

Now I want single sql query that will retrieve all the records for the parent part ‘CF061W’.

Thanks in advance

Pradeep

Answer 1

This is almost a textbook example of when to use a Recursive CTE.

There are plenty of articles detailing what to do. eg. this one on MSDN: http://msdn.microsoft.com/en-us/library/ms186243.aspx

Answer 2

I might have the join condition columns: INNER JOIN PartHierarchy ph ON n.finum = ph.fparinum the wrong way round (not familiar with your schema).

WITH PartHierarchy (finum, part, fparinum , dsono, flevel) AS
(
   -- Base case
   SELECT
      finum, 
      part, 
      fparinum, 
      dsono, 
      1 as flevel
   FROM myTablename
   WHERE fparinum  = 0

   UNION ALL

   -- Recursive step
   SELECT
      n.finum, 
      n.part, 
      n.fparinum, 
      n.dsono, 
      ph.flevel + 1 AS flevel
   FROM myTablename n
      INNER JOIN PartHierarchy ph ON n.finum = ph.fparinum 
)

SELECT *
FROM PartHierarchy 
ORDER BY flevel 

This is a classic recursive CTE (Common Table Expression)

Answer 3

this wil work for you

WITH TAB_CTE AS (
SELECT finum, part, fparinum, flevel
FROM TABTEST
WHERE  PART='CF061W'
UNION ALL
SELECT e.finum, e.part, e.fparinum,   e.flevel
FROM TABTEST e
INNER JOIN TAB_CTE ecte ON ecte.finum = e.fparinum 
)
SELECT *
FROM TAB_CTE

OUTPUT

finum       part           fparinum flevel
25          CF061W          0           1
26          FA061w          25          2
27          hrd20600        25          2
35            sd1201        25              2
28          f1024           27          3