CODEWITHSUNDEEP
sqlsql-servert-sql
Kric
Kric

Reputation: 103

SQL Find Parent ID for Certain Record Type

I have a table like following:

ID         Type      ParentID
001         C          007
002         C          005
003         B          007
004         C          008
005         R          NULL
006         C          004
007         B          004
008         R          009
009         X          NULL

The type hierarchy is X>R>C=B. I need to find all the records' R parent for B and C. The challenge is some of the B or C records' parents are B or C and X needs to be excluded. Results would be:

ID         Type      ParentID     MasterParentID
001         C          007           008
002         C          005           005
003         B          007           008
004         C          008           008
005         R          NULL          NULL
006         C          004           008
007         B          004           008 
008         R          009           NULL
009         X          NULL          NULL

Any suggestions? Much appreciated.

Upvotes: 0

Views: 444

Answers (3)

roman
roman

Reputation: 117400

you need recursive CTE:

with cte1 as (
    select
       T.ID, T.Type, T.ParentID,
       case
          when T2.Type = 'X' then cast(null as varchar(3))
          else T.ParentID
       end as MasterParentID
    from Table1 as T
        left outer join Table1 as T2 on T2.ID = T.ParentID
), cte2 as (
    select
        T.ID, T.Type, T.ParentID,
        T.MasterParentID, T.ID as MasterParentID2
    from cte1 as T
    where T.MasterParentID is null

    union all

    select
        T.ID, T.Type, T.ParentID,
        c.MasterParentID2 as MAsterParentID,
        c.MasterParentID2 as MAsterParentID2
    from cte1 as T
        inner join cte2 as c on c.ID = T.MasterParentID
)
select
    T.ID, T.Type, T.ParentID, T.MasterParentID
from cte2 as T
order by ID asc

sql fiddle demo

Upvotes: 1

Vasanth
Vasanth

Reputation: 1710

Here is the query to achieve that using CTE, also a working demo

  ;with Childs as
  (
      select ID, Type, ParentID, 0 Level
      from SomeHierarchy
      union all
      select Childs.ID, SomeHierarchy.Type, SomeHierarchy.ParentID, Childs.Level + 1 Level
      from 
          SomeHierarchy
          inner join Childs on Childs.ParentID = SomeHierarchy.ID
      where
          SomeHierarchy.Type not in ('R', 'X')

  )
  select 
    SomeHierarchy.ID, SomeHierarchy.Type, SomeHierarchy.ParentID, NewChilds.ParentID MasterParentID
  from SomeHierarchy
    inner join (
                  select *, Row_Number() over(Partition by ID order by Level desc)  RowNum
                  from Childs
                  where
                    ParentID is not null or Level = 0
                ) NewChilds on NewChilds.ID = SomeHierarchy.ID
  where
    NewChilds.RowNum = 1

Upvotes: 1

David
David

Reputation: 34563

In SQL Server 2005 and above, a "common table expression" can do this...

-- Assuming this table structure
--CREATE TABLE dbo.Test ( ID char(3), Type char(1), ParentID char(3))

;
WITH Tree(StartID, StartType, Parent, Child) AS
(
    SELECT ID, Type, cast(NULL as char(3)), ID
    FROM Test
    WHERE ParentID IS NULL

    UNION ALL

    SELECT
        -- Skip over the "X" rows...
        CASE WHEN Tree.StartType = 'X'
            THEN Test.ID
            ELSE Tree.StartID
        END,
        CASE WHEN Tree.StartType = 'X'
            THEN Test.Type
            ELSE Tree.StartType
        END,
        Test.ParentID,
        Test.ID
    FROM Test
        INNER JOIN Tree
        ON Test.ParentID = Tree.Child
)

SELECT Test.ID, Test.Type, Test.ParentID,
    CASE WHEN Tree.StartID = Test.ID
        THEN NULL
        ELSE Tree.StartID
    END AS MasterParentID
FROM Test
    LEFT OUTER JOIN Tree
    ON Test.ID = Tree.Child
ORDER BY Test.ID

Upvotes: 1

Related Questions