CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Haminteu
Haminteu

Reputation: 1334

Get All Child SQL

I have a table like:

oName     oPost    oPoint
A         1        NULL
B         2        1
C         3        1
D         4        1
E         5        2
F         6        2
G         7        3

So, If I select oName = A then the result should be:

oName
A
B
C
D
E
F
G

If I select oName = B, then the result should be:

oName
B
E
F

How can I do this on SQL?

Upvotes: 0

Views: 43

Answers (1)

Nick
Nick

Reputation: 147166

You can use a recursive CTE to traverse the hierarchy starting at your specified node:

DECLARE @name VARCHAR(1);
SET @name = 'A';

WITH CTE AS (
  SELECT oName, oPost
  FROM data
  WHERE oName = @name
  UNION ALL
  SELECT d.oName, d.oPost
  FROM data d
  JOIN CTE ON CTE.oPost = d.oPoint
)
SELECT oName
FROM CTE
ORDER BY oName

Output:

oName
A
B
C
D
E
F
G

Or, if you SET @name='B', output is

oName
B
E
F

Demo on dbfiddle

Upvotes: 2

Related Questions