CODEWITHSUNDEEP
sql-servert-sql
Anitha Anbalagan
Anitha Anbalagan

Reputation: 21

displaying column based on value of another column

There is a table like this:

Parent | Child
1      | 11
11     | 12
11     | 13
12     | 14
12     | 14

If pass 1, it should return all the ‘1’ children’s: 11, 12, 13, 14

Upvotes: 0

Views: 374

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138980

-- Test data
declare @T table (ParentID int, ChildID int)
insert into @T values(1,       11)
insert into @T values(11,      12)
insert into @T values(11,      13)
insert into @T values(12,      14)
insert into @T values(12,      14)

-- ID to search for
declare @ParentID int = 1

-- Recorsive cte
;with cte as
(
  select T.*
  from @T as T
  where T.ParentID = @ParentID
  union all
  select T.*
  from cte as C
    inner join @T as T
      on C.ChildID = T.ParentID
)
select distinct ChildID
from cte

Result

ChildID
-------
11
12
13
14

Upvotes: 0

Nils Magne Lunde
Nils Magne Lunde

Reputation: 1824

You could have a look at recursive queries using CTE. Links about the topic:

Upvotes: 2

Related Questions