Recursive retrieve of child entities but with exact property match

Question

I have the following tables:

Companies table:

CompanyId       MotherCompanyId   CompanyName     
----------      ------------     ------------   
  1             NULL             HpTopMother             
  2             1                HpTopDaughter1           
  3             2                HpTopDaughter2           
  4             3                HpTopDaughter3  

CompanyCategories table:

CompanyCategoryId       CompanyId    Category     
-----------------       --------    -----------    
  1                     1           Car                          
  2                     1           Lorry                  
  3                     2           Car      
  4                     2           Lorry
  5                     2           Plane
  6                     3           Car
  7                     3           Lorry
  8                     4           Car

What I want to do is to display all the daughter companies of the head company (the Id will be passed as paramter), that have the exact match in the CompanyCategories table.
For example, in the case above only the head company, HpTopMother, and daughter company with Id 3, HpTopDaughter2, will be displayed as both have the categories Car and Lorry.

HpTopDaughter1 will not be retrieved since it has the Plane category.

HpTopDaughter3 will not be retrieved since it does not have the Car category.

I have done the below to get all the daughters/grand-daughters of the head company:

DECLARE @companyId BIGINT
SET @companyId = 1;

WITH CTE AS 
 (
  SELECT COM.CompanyId, COM.CompanyName
  FROM Companies COM
  WHERE COM.CompanyId = @companyId
  UNION ALL
  SELECT COM_CHILD.CompanyId, COM_CHILD.CompanyName
  FROM Companies COM_CHILD JOIN cte c ON COM_CHILD.MotherCompanyId = c.CompanyId
  INNER JOIN CompanyCategories CC ON CC.CompanyId = c.CompanyId
  INNER JOIN CompanyCategories CC_CHILD on CC_CHILD.CompanyId = COR_CHILD.CompanyId and CC.Category = CC_CHILD.Category
  )
  SELECT  CompanyId, CompanyName
  FROM CTE

However, this is returning all the companies. Any idea of how I can achieve the listing of all the daughters/grand-daughters companies but only those having an exact category match?

Answer 1

Use CTE to search for parent-child chains and then filter out companies according to your condition using Not Exist and Except.

Declare @ID Int = 1;
    
With A As 
(   Select CompanyId, CompanyName, Row_Number() Over (Order by CompanyId) As Num
    From Companies
    Where CompanyId = @ID
    Union All 
    Select Companies.CompanyId, Companies.CompanyName, A.Num
    From Companies Inner Join A On (Companies.MotherCompanyId=A.CompanyId)
)
Select A.Num, A.CompanyName, String_Agg(C.Category,',') As Categories
From A Inner Join A As A_1 On (A.Num=A_1.Num)
       Inner Join CompanyCategories As C On (A.CompanyId=C.CompanyId)
Where A.CompanyId<>A_1.CompanyId And 
        Not Exists (Select Category From CompanyCategories
                    Where A_1.CompanyId=CompanyCategories.CompanyId
                    Except
                    Select Category From CompanyCategories
                    Where A.CompanyId=CompanyCategories.CompanyId) And
        Not Exists (Select Category From CompanyCategories
                    Where A.CompanyId=CompanyCategories.CompanyId
                    Except
                    Select Category From CompanyCategories
                    Where A_1.CompanyId=CompanyCategories.CompanyId)
Group by A.Num, A.CompanyName
Order by A.Num, A.CompanyName Desc

db<>fiddle

Result

Num	CompanyName	Categories
1	HpTopMother	Car,Lorry
1	HpTopDaughter2	Car,Lorry

to get companies with the same categories as the company associated with @ID, use the following query:

Declare @ID Int = 1;

With A As 
(   Select CompanyId, CompanyName, Row_Number() Over (Order by CompanyId) As Num
    From Companies
    Where CompanyId = @ID
    Union All 
    Select Companies.CompanyId, Companies.CompanyName, A.Num
    From Companies Inner Join A On (Companies.MotherCompanyId=A.CompanyId)
)
Select A.Num, A.CompanyName, String_Agg(C.Category,',') As Categories
From A Inner Join A As A_1 On (A.Num=A_1.Num)
       Inner Join CompanyCategories As C On (A_1.CompanyId=C.CompanyId)
Where A_1.CompanyId = @ID
      And Not Exists (Select Category From CompanyCategories
                      Where A_1.CompanyId=CompanyCategories.CompanyId
                      Except
                      Select Category From CompanyCategories
                      Where A.CompanyId=CompanyCategories.CompanyId)
      And Not Exists (Select Category From CompanyCategories
                      Where A.CompanyId=CompanyCategories.CompanyId
                      Except
                      Select Category From CompanyCategories
                      Where A_1.CompanyId=CompanyCategories.CompanyId)
Group by A.Num, A.CompanyName
Order by A.Num, A.CompanyName Desc