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?

CodePudding user response：

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

With A As 
(   Select CompanyId, CompanyName, Row_Number() Over (Order by CompanyId) As Num
    From Companies
    Where MotherCompanyId Is Null
    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

CodePudding user response：

Check if category sets are the same for the top level company and its descendants

DECLARE @companyId BIGINT
SET @companyId = 1;

WITH rcats as(
 select  null a, 1 b, category  
 from CompanyCategories CC 
 where CC.companyId = @companyId
), 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
)
SELECT  CompanyId, CompanyName
FROM CTE c
where not exists (
    select 1
    from
       ( select 1 a, null b, category
        from CompanyCategories CC 
        where CC.CompanyId = c.CompanyId
        union all
        select a, b, category
        from rcats
        ) t
     group by category  
     having count(a) <> count(b)
     )

db<>fiddle