Is it possible to replace cursor in SQL when the query is hierarchical?

Question

I have three tables in MsSQL to store data.

First one is the Children contains the following rows ID, Name, Age, FatherID, SchoolID

The second one contains the data of their fathers, like this FatherID, Name, Age

The third one is the School with SchoolID and SchoolName, Address, etc.

(The relation is: multiple children can have the same father, obviously :) )

What I want to list is all the family members related to a given SchoolID.

I know a cursor based solution:

*Cursor is working with two temporary tables, at first step selecting the children with a given schoolid into one temporary table(@tempfamilymembers).

Second step is to select the distinct fatherids from this table and save it to the second temporary table(@fatherids).

Then, I would loop over these fatherids to insert fathers with matching fatherids(from @fatherids) into the first temporary table(@tempfamilymembers).

Then, I can select all from @tempfamilymembers to complete the query*

The cursor based solution is too difficult, and relatively slow plus I heard while loops are showing better performance and a set-based approach would be even better.

My question is : Can I achieve this somehow, without creating a cursor? (I want to list the fathers present in this list only one time, even if he has more than one children.)

Answer 1

WITH family_members (id, name, age, role) AS
(
  SELECT FatherID, Name, Age, 'father'
  FROM Fathers 
  WHERE FatherID IN(SELECT DISTINCT FatherID
                    FROM Children 
                    WHERE SchoolID = 1) -- 1) Put School to search here
  UNION ALL
  SELECT ID, Name, Age, 'child '
  FROM Children
  WHERE SchoolID = 1 -- 2) Put School to search here
)
SELECT * FROM family_members

• If you were to put some data in rextester you could test this out. Let me know what you think.

• If columns are of differing types (Children vs Father) you may have to CAST columns.

• As I mentioned originally in comments CTE's/UNION ALL are way to go. ;-)