CODEWITHSUNDEEP
sqlsql-servert-sql
Pradeep
Pradeep

Reputation: 2729

SQl query required for the below Scenario

alt text

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

Upvotes: 0

Views: 106

Answers (3)

Thakur
Thakur

Reputation: 2020

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

Upvotes: 1

Mitch Wheat
Mitch Wheat

Reputation: 300549

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)

Upvotes: 1

Andrew Carmichael
Andrew Carmichael

Reputation: 3113

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

Upvotes: 0

Related Questions