Design a recursive / hierarchical query

Question

I need your help with this request, I can’t decide. Here's what, say, I have a table like this:

Father | Son 1 | 2 1 | 3 1 | 4 3 | 5 3 | 6 2 | 7 4 | 8 5 | 9 6 | 10 

So, as you see, the table represents the relationship of father and son, I need to find all the sons (and sons of the sons) of one father, for example, if Im looking for sons of the number 1, we get a table like this

 Father | son 1 | 2 1 | 3 1 | 4 1 | 5 1 | 6 1 | 7 1 | 8 1 | 9 1 | 10 

If I look at sons from number 3, we get:

 Father | son 3 | 5 3 | 6 3 | 9 3 | 10 

Well, I think you understand my point. The next problem is that I have to do this for every father in the table. Therefore, at the end, the table will look like this:

 Father | son 1 | 2 1 | 3 1 | 4 1 | 5 1 | 6 1 | 7 1 | 8 1 | 9 1 | 10 3 | 5 3 | 6 3 | 9 3 | 10 2 | 7 4 | 8 5 | 9 6 | 10 

pretty similar to a tree. I tried to find out this query for a while without having time. I made code like this

 WITH son AS ( -- in SELECT FK_CC_B FROM PG_Rols_CC WHERE FK_CC_A = @idCC UNION ALL --recur SELECT t.FK_CC_B FROM PG_Rols_CC AS t JOIN hijos AS a ON t.FK_CC_A = a.FK_CC_B ) insert into @TableSons (idSon) SELECT * FROM son 

But this does not work for each father at the same time, and the final table is slightly different, she only has the identifier of the son. Example

 son 3 4 5 6 7 

I hope you help me.

Answer 1

If you want to get descendants of all fathers, you need to get rid of the WHERE in the first part of your common table expression (CTE).

If you want to output two columns (Father and Offspring), you need to add these columns to both sides of the CTE.

Like this:

 WITH Son AS ( SELECT FK_CC_A AS Father, FK_CC_B AS Son FROM PG_Rols_CC UNION ALL SELECT f.Father, s.FK_CC_B AS Son FROM Son f INNER JOIN PG_Rols_CC s ON f.Son = s.FK_CC_A ) SELECT * FROM Son 

Answer 2

You need to refer to the "Anchor" part from the "recursive" member ...

Something like this work:

 WITH son AS ( -- in SELECT @Father, Son FROM PG_Rols_CC WHERE FK_CC_A = @Father UNION ALL --recur SELECT @Father, Son FROM PG_Rols_CC AS t JOIN son AS s ON s.FK_CC_A = t.FK_CC_B ) SELECT * FROM son