Select all parents or children in same table relation SQL Server

Question

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.

Answer 1

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.

Answer 2

here is a recursive query giving you both all ancestors and all descendants of an element. Use these together or separate according to the situation. Replace the where clauses to get the desired record. In this example I am looking for key 13 (this is the element with name = b) and find its ancestor 12/a and its descendant 14/c.

with all_ancestors(relation, version, name, elem_key, parent_key, dist_key)
as 
(
  -- the record itself
  select 'self      ' as relation, self.version, self.name, self.elem_key, self.parent_key, self.dist_key
  from elem self
  where elem_key = 13
  union all
  -- all its ancestors found recursively
  select 'ancestor  ' as relation, parent.version, parent.name, parent.elem_key, parent.parent_key, parent.dist_key
  from elem parent
  join all_ancestors child on parent.elem_key = child.parent_key
)
, all_descendants(relation, version, name, elem_key, parent_key, dist_key)
as 
(
  -- the record itself
  select 'self      ' as relation, self.version, self.name, self.elem_key, self.parent_key, self.dist_key
  from elem self
  where elem_key = 13
  union all
  -- all its descendants found recursively
  select 'descendant' as relation, child.version, child.name, child.elem_key, child.parent_key, child.dist_key
  from elem child
  join all_descendants parent on parent.elem_key = child.parent_key
)
select * from all_ancestors
union
select * from all_descendants
order by elem_key
;

Here is the SQL fiddle: http://sqlfiddle.com/#!6/617ee/28.

Answer 3

I've created a function for finding the parents of a specific child where you have to pass the Id of the child.

This will return the list of parents as a comma separated string. Try this if it works for you.

I'm assuming that the parent_key with null value is root.

CREATE FUNCTION checkParent(@childId INT)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @parentId VARCHAR(MAX) = NULL
    DECLARE @parentKey INT = null
    SET @parentId = (SELECT parent_key FROM Elem WHERE [KEY] = @childId)

    WHILE(@parentKey IS NOT NULL)
    begin
        SET @parentId = @parentId +  ', ' + (SELECT parent_key FROM Elem WHERE [KEY] = @parentId)
        SET @parentKey = (SELECT parent_key FROM Elem WHERE [KEY] = @parentId)
    END
    RETURN @parentId
END
GO

Answer 4

I dont think it can be done in one select in whole case ,so that you can select all parents,grandparents ,... . One way how to do it is to join elem table to herself ,and thet it depends on how many levels of join you do , that level of childs,grandchilds will you get.

The solution can be somethink like this(for the second case )

this will select you all parents,childs and grandchilds

Select 
parent.key as parent_key,
child.key as child_key,
grandchild.key as grandchild_key 
from elem parent 
join elem child on (elem.key=child.parentkey)
join elem grandchild on (child.key=grandchild.parentkey)
where parent.parentkey is null; -- this make you sure that first level will be parents

solution for the first case is just that you will connect tables not in style of 'key=parentkey' but oposite 'parentkey=key'.