SQL developers, I have a badly planned database as task to learn a lot about SQL Server 2012.

SO, there is the table Elem:

+-----------+----+---+----------+------------+
|VERSION(PK)|NAME|KEY|PARENT_KEY|DIST_KEY(FK)|
+-----------+----+---+----------+------------+
|1          |a   |12 |NULL      |1           |
+-----------+----+---+----------+------------+
|2          |b   |13 |12        |1           |
+-----------+----+---+----------+------------+
|3          |c   |14 |13        |1           |
+-----------+----+---+----------+------------+
|4          |d   |15 |12        |1           |
+-----------+----+---+----------+------------+
|5          |e   |16 |NULL      |1           |
+-----------+----+---+----------+------------+
|6          |e   |17 |NULL      |2           |
+-----------+----+---+----------+------------+

After update the row I need to check parent key of element to not allow element to be self-granny or something..

And when I delete the row I need to delete all children and children of children, etc.

Questions are:

1. How can i select all "parent + grandparent + etc" of one element of DIST?

2. How can i selects all "sons + grandsons + etc" of one element of DIST?

I read about solutions with CTE, but I have no root of elements and I can't even understand how I can use CTE then.

Please, help!

Thanks.

I have met this problem，I resolved problem by this way

 --all  "parent + grandparent + etc" @childID Replaced with the ID you need

with tbParent as
(
   select * from Elem where [KEY]=@childID
   union all
   select Elem.* from Elem  join tbParent  on Elem.[KEY]=tbParent.PARENT_KEY
)
 SELECT * FROM  tbParent
 --all "sons + grandsons + etc" @parentID Replaced with the ID you need

with tbsons as
(
  select * from Elem where [KEY]=@parentID
  union all
  select Elem.* from Elem  join tbsons  on Elem.PARENT_KEY=tbsons.[KEY]
)
SELECT * FROM tbsons

PS.My English is not good.