Query returning all the children, sibling and parent from a single Id

Question

I have a table, Company:

CompanyId       MotherCompanyId   CompanyName     
----------      ------------     ------------   
  1             NULL             HpTopMother             
  2             1                HpTopDaughter1           
  3             1                HpTopDaughter2           
  4             NULL             HpTopDaughter3  
  5             2                HpTopDaughter4           
  6             1                HpTopDaughter5

What I want to do is, from an Id, to return the parent of that Id (if any), its children, and its siblings. So if I have CompanyId = 1, the query will return me

Id: 2,3,6

And if I have CompanyId = 2, the query will return me

Id: 1,3,5,6

I have tried something like:

DECLARE @cmpId BIGINT
SET @cmpId = 14085;

WITH CTE_FAMILY as(
    -- To get the siblings
    select CompanyId       
    from Company 
    where MotherCompanyId    = (select MotherCompanyId    from Company where CompanyId        = @cmpId)
    -- To get the daughters
    UNION ALL 
    SELECT CompanyId       
    FROM Company
    WHERE MotherCompanyId    = @corId
)

SELECT *
FROM CTE_FAMILY

But this is returning only Id: 3,6 when I have CompanyId = 2.

Any idea of how to do what I want please?

Answer 1

Find each type you need and union all together

WITH CTE_FAMILY as
(    
    select CompanyId = c.MotherCompanyId, type = 'Parent'
    from   Company c
    where  c.CompanyId = @cmpId 
    and    c.MotherCompanyId is not null

union all

    select CompanyId = s.CompanyId, type = 'Sibling'
    from   Company c
           inner join Company s on c.MotherCompanyId = s.MotherCompanyId
    where  c.CompanyId = @cmpId 
    and    c.MotherCompanyId is not null
    and    c.CompanyId <> s.CompanyId

union all

    select CompanyId = c.CompanyId, type = 'Child'
    from   Company c
    where  c.MotherCompanyId = @cmpId 
)
select *
from   CTE_FAMILY 

Answer 2

Like an alternative, a query can be like this

WITH subject AS (
    SELECT * 
    FROM Company 
    WHERE CompanyId = @cmpId
),
parent AS (
    SELECT Company.* 
    FROM Company, subject 
    WHERE Company.CompanyId = subject.MotherCompanyId
),
direct_children AS (
    SELECT Company.* 
    FROM Company, subject 
    WHERE Company.MotherCompanyId = subject.CompanyId
),
siblings AS (
    SELECT Company.* 
    FROM Company, subject, parent 
    WHERE Company.MotherCompanyId = parent.CompanyId AND Company.CompanyId != subject.CompanyId
),
family AS (
    SELECT * FROM parent
    UNION ALL
    SELECT * FROM direct_children
    UNION ALL
    SELECT * FROM siblings
)
SELECT * FROM family

Demo

Answer 3

To present an alternative approach, here's a solution that uses hierarchyid. First, the setup:

drop table if exists #d;

with d as (
    select * from (values
        (1, NULL, 'HpTopMother'),
        (2, 1, 'HpTopDaughter1'),
        (3, 1, 'HpTopDaughter2'),
        (4, NULL, 'HpTopDaughter3'),
        (5, 2, 'HpTopDaughter4'),
        (6, 1, 'HpTopDaughter5')
    ) as x(CompanyId, MotherCompanyId, CompanyName)
),
rcte as (
    select *, 
        [path] = cast(concat('/', CompanyID, '/') as varchar(100))
    from d
    where MotherCompanyId is null

    union all

    select child.*, 
        [path] = cast(concat(parent.[path], child.CompanyId, '/') as varchar(100))
    from d as child
    join rcte as parent
        on child.MotherCompanyId = parent.CompanyId
)
select CompanyId, MotherCompanyId, CompanyName, cast([path] as hierarchyid) as [path]
into #d
from rcte;

By way of brief explanation, all I'm doing here is recreating your data and then doing the same recursive CTE dance to get the hierarchy. Where this approach starts to diverge is that I'm persisting the results of that. This could be done once in your actual table an maintained by your application as and when data changes. Note, that this doesn't mean running the recursive CTE again but rather if you know the parent (which already has a value for [path]) and the child, you can construct a path value for the child; no need to traverse the hierarchy back to the root.

Now that the hard work is done, we can query up (for parents, grandparents, etc), down (for children, grandchildren, etc), or laterally (for siblings).

declare @CompanyID int = 2;
declare @CompanyPath hierarchyid = (
    select [path]
    from #d
    where CompanyId = @CompanyID
);
declare @ParentCompanyPath hierarchyid = @CompanyPath.GetAncestor(1);

select *
from #d as d
where d.CompanyId <> @CompanyID /* don't return self */
    and (
        /* find any parent, grandparent, etc companies */
        d.[path].IsDescendantOf(@CompanyPath) = 1

        /* find any child, grandchild, etc companies */
        or @CompanyPath.IsDescendantOf(d.[path]) = 1 
        or (
            /* find any row that has a shared parent */
            d.[path].IsDescendantOf(@ParentCompanyPath) = 1 

            /* and our 'distance' from said the root 
               (and by extension the parent) is the same */
            and d.[path].GetLevel() = @CompanyPath.GetLevel() 
        )
    );

Note, you have choices as to how to write this. For example, I could have used join syntax instead (obviating the need to determine @CompanyPath or @ParentCompanyPath separately). Or each predicate could have been its own query and use union to jam them all together.

I didn't do it here because the result set is trivially sized, but you can put an index on hierarchyid columns which makes these sorts of queries efficient over non-trivial data sets.