I need to write a recursive SQL query to fetch multiple projects together with their lineage from the following table:
CREATE TABLE #Project ( Name VARCHAR(10), ParentName VARCHAR(10) ) INSERT INTO #Project(Name, ParentName) VALUES ('P', NULL); INSERT INTO #Project(Name, ParentName) VALUES ('C1', 'P'); INSERT INTO #Project(Name, ParentName) VALUES ('C2', 'P'); SELECT Name, ParentName FROM #Project
(this is just a toy example, so please ignore table design issues and the lack of indexing)
So, given multiple project names, I need to fetch a table that contains the projects as well as all their ancestors, because the result table will have to support lineage queries for a given Project name
In order to do this, I came up with the following recursive CTE query:
WITH Lineage (Name, ParentName) AS ( SELECT Name, ParentName FROM #Project WHERE Name = 'C1' OR Name = 'C2' UNION ALL SELECT #Project.Name, #Project.ParentName FROM #Project INNER JOIN Lineage ON #Project.Name = Lineage.ParentName ) SELECT Name, ParentName FROM Lineage
And, here is the result of the query:
Name | ParentName C1 | P C2 | P P | NULL P | NULL
As expected, this returns projects C1 and C2, as well as their parents, which will be duplicated for each child, unfortunately.
In order to get rid of the duplicated parents, I added
DISTINCT to the final
SELECT DISTINCT Name, ParentName FROM Lineage
Name | ParentName P | NULL C1 | P C2 | P
Even though this produces the desired result, I am concerned about the performance of this query. The Lineage CTE will surely contain a lot of extra rows if I let it return these duplicates, which I remove at a later step in the query.
Is there any way to rewrite the CTE such that it removes these duplicates directly?
UPDATE: Just in case my example wasn't clear enough, I need to somehow specify the project names for which to fetch the lineages.