recursive CTE - limited numbers of rows

Question

Hi

I'm trying to create a query on a very simple table (organisations)

I have the columns

Organisation, Manager, Superior_Organisation
CEO
Leadership Team, David, CEO 
Production Management, Alex, Leadership Team
Production Site 1, Francoise, Production Management
Production Site 2, Steve, Production Management
Production Site 1 Maintenance, Alan, Production Site 1

....

Because of different level, I don't know how to create a query, that delivers me all superior organisations, starting at one specific level

I tried this code

declare @i int
select @i = 0
-- keep going until no more rows added
while @@rowcount > 0
begin
select @i = @i + 1
-- Get all children of previous level
SELECT     organisations.Organisation, organisations.Manager,  
organisations.Superior_Organisation
FROM         organisations 
end

But with this query, I get all and I don't know, how I can query only the superior orgs e.g. for Production Site 1 Maintenance. (can be 1 or up to 5)

One way could be a join on the table but I think, that's far away from being performant.

I've seen some recursive CTE queries, but I'm not familiar. So appreciate help.

Answer 1

;WITH organisations AS
(
SELECT 'CEO' AS Organisation, cast(NULL as varchar(50)) AS Manager, cast(NULL as varchar(50)) AS Superior_Organisation UNION ALL
SELECT 'Leadership Team', 'David', 'CEO'  UNION ALL
SELECT 'Production Management', 'Alex', 'Leadership Team' UNION ALL
SELECT 'Production Site 1', 'Francoise', 'Production Management' UNION ALL
SELECT 'Production Site 2', 'Steve', 'Production Management' UNION ALL
SELECT 'Production Site 1 Maintenance', 'Alan', 'Production Site 1'
),
cte As (
SELECT Organisation, Manager, Superior_Organisation
FROM         organisations 
WHERE organisations.Organisation = 'Production Site 1 Maintenance'
UNION ALL
SELECT o.Organisation, o.Manager, o.Superior_Organisation
FROM         organisations o
JOIN cte ON cte.Superior_Organisation = o.Organisation
)
SELECT Organisation, Manager, Superior_Organisation
FROM cte
WHERE Organisation <> 'Production Site 1 Maintenance'