I need to know how the Recursive Common Table Expression works
I have a table called Project table which consists of a collection of projects some of them are parent projects and the other are child projects also the child projects may be parent for other child projects and so on. The table scheme look like this:
ProjectID BIGINT, CodeOfProject BIGINT, NameOfProject NVARCHAR, SuperProjectID BIGINT, IsProjectMajor BIT
Where the column IsProjectMajor determines whether this project is the parent of another project or not. As it is when its value is false, it means that this project is not a parent and will not be a parent of any projects (i.e, a leaf project)
Based on what I explained it is possible to happens that a project A be a parent project for the project B which in turn is the parent project for the project C all of the parent projects (even if they are childs for another projects have the value true in the column IsProjectMajor). I need to get only the leaf projects in a report and due to this parental relationship I do not know how many parent in chain for a given leaf project for the above example there be project D that is the child and also a leaf project for project C. So I need when to search by any of direct or indirect parent project of project D I got the project D
What I made is using the Recursive Common Table Expression by getting all projects that have the IsProjectMajor with value true (all parent projects) from the Projects table then I UNION ALL with the Projects table with INNER JOIN to the Recursive Common Table base on SuperProjectID and the ProjectID columns but of course the result is chaotic. Lets give an example:
Project A parent Of Project B,
Project B parent of Project C,
Project C parent Of Project D,
Project D parent of Projects E, and F
I need to know how the recursive process done as it was not as I expected and my expectations were that the recursive process done by getting a parent project and gets all it childs then go to the next parent project and so on untill all the parent projects selected, so it will get project A and gets its child which is B, then there were no other childs for A, so the next step is to take project B and do the same, then project C, then project D, until it reached project E, and F