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

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?

CodePudding user response：

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

CodePudding user response：

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