How to find grandparent parent child relationship for View in SQL

Question

With a table that looks like this:

---

ParentID	AgencyID	CompanyName
NULL	1	ABC Agency
NULL	2	Another Agency
2	3	Agency 3
3	4	Agency 4

The goal here is to develop a database view that shows the parent and grand-parent for a given agency. Some agencies only have a parent (no grand-parent), and still others are stand-alone - they don't have a parent. We want the view to look like this:

- GrandParentAgencyNo
- GrandParentName
- ParentAgencyNo
- ParentName 
- AgencyNo 
- AgencyName
- NumberOfChildren
- NumberOfGrandChildren

We could use that to find all the children for a given agency:

    select * from AgencyView where ParentAgencyNo = "ABC123"

if an agency doesn't have a parent, the above result should look like this:

- GrandParentAgencyNo: 1 
- GrandParentName: ABC Agency 
- ParentAgencyNo: 1
- ParentName: ABC Agency 
- AgencyNo: 1
- AgencyName: ABC Agency 
- NumberOfChildren: 0
- NumberOfGrandChildren: 0

I tried writing recursive functions similar to the one below (including other queries that included trying to find the grandparent) with no luck. I am unfamiliar with recursion and always seem to hit the max recursion rate in SQL Server.

with A(Id, ParentId) as 
(
    select AgencyId, ParentAgencyId from Agency 
    union all
    select e.AgencyId, p.ParentId from Agency e 
    join A p on e.ParentAgencyId = p.Id
)
select * from A
OPTION (MAXRECURSION 32767)

Answer 1

I don't use recursion unless I have to, usually due to an unknown or large number of levels. I would simply use left joins and case statements to get parents and grandparents and a sub-select for the child counts.

SELECT A.Agencyid, A.CompanyName
, CASE WHEN p.AgencyId is not null then p.AgencyId 
       else A.AgencyID END AS ParentAgencyId
, CASE WHEN p.AgencyId is not null then p.CompanyName
       else A.CompanyNameEND ParentCompanyName
, CASE WHEN gp.AgencyId is not null then gp.AgencyId 
       WHEN p.AgencyId is not null then p.AgencyId 
       else A.AgencyID END GrandParentAgencyId
, CASE WHEN gp.AgencyId is not null then gp.CompanyName
       WHEN p.AgencyId is not null then p.CompanyName
       else A.CompanyNameEND GrandParentCompanyName
, (SELECT count(*) FROM Agency where ParentId = A.AgencyId) Children
, (SELECT count(*) FROM Agency C 
       JOIN Agency GC ON C.AgencyId = GC.ParentId 
       where C.ParentId = A.AgencyId) GrandChildren
FROM Agency A
LEFT JOIN Agency P ON P.AgencyId = A.ParentId
LEFT JOIN Agency GP ON GP.AgencyId = P.ParentId
WHERE A.AgencyId = 1