Recursive function parent-child in t-sql

Question

I have two tables and I want to do a recursive query in order to get a final table with a parent-child relationship and a column saying if it has childs. My idea was to join both tables and use a use CTE (the query give me an error I attach it) and an image of what I want to do.

DESCRIPTION Table 1 and 2 show us the states of an Account and Operation, table 1 has all "Product" 999 and table 2 has all "Product" 39. The main idea of this, is to look up for the origen of the Account and Operation with "Product"999 in table 1, for example:

In table 1,

• First, the row with Product_Ini = 999 / Acccount_Ini = 777 / Operation_Ini = 888 came from Product_Fin = 999 / Acccount_Fin = 777 / Operation_Fin = 666
• Second, the idea is to look up for the origen of Product_Fin = 999 / Acccount_Fin = 777 / Operation_Fin = 666, in order to do this i do a CTE in the same table and i find Product_Fin = 39 / Acccount_Fin = 126/ Operation_Fin = 333
• Third, the idea is to look up for the origen of Product_Fin = 39/ Acccount_Fin = 126 / Operation_Fin = 333, in order to do this i have to use table 2 and search there, having the result Product_Fin = 32 / Acccount_Fin = 126/ Operation_Fin = 858
• Finally, i want to get a table showing the evolution of an account, with a column Child that means if the account/operation has childs.

QUERY:

drop table #table_1
go
create table #table_1
(
    Product_Ini int,
    Account_Ini int,
    Operation_Ini   int,
    Product_Fin int,
    Account_Fin int,
    Operation_Fin int
)
go

drop table #table_2
go
create table #table_2
(
    Product_Ini int,
    Account_Ini int,
    Operation_Ini   int,
    Product_Fin int,
    Account_Fin int,
    Operation_Fin int
)
go

insert into #table_1 values (999, 123, 456, 32  ,123, 426)
insert into #table_1 values (999, 123, 456, 23  ,123, 159)
insert into #table_1 values (999, 123, 456, 65  ,123, 486)
insert into #table_1 values (999, 596, 162, 32  ,596, 263)
insert into #table_1 values (999, 126, 529, 999 ,126, 459)
insert into #table_1 values (999, 126, 459, 32  ,126, 784)
insert into #table_1 values (999, 126, 741, 999 ,126, 852)
insert into #table_1 values (999, 126, 852, 999 ,126, 111)
insert into #table_1 values (999, 126, 111, 999 ,126, 333)
insert into #table_1 values (999, 126, 333, 32  ,126, 995)
insert into #table_1 values (999, 523, 542, 999 ,523, 478)
insert into #table_1 values (999, 777, 888, 999 ,777, 666)
insert into #table_1 values (999, 777, 666, 39  ,126, 333)
insert into #table_1 values (999, 899, 565, 39  ,899, 474)
insert into #table_1 values (999, 565, 145, 39  ,565, 424)
insert into #table_1 values (999, 565, 361, 85  ,565, 452)
----
insert into #table_2 values (39, 126, 333, 32   ,126, 858)
insert into #table_2 values (39, 899, 474, 999  ,899, 525)
insert into #table_2 values (39, 565, 424, 999  ,565, 361)
--

select * from #table_1
select * from #table_2

;WITH ctetable(depth, Product_Ini, Account_Ini, Operation_Ini, Product_Fin, Account_Fin, Operation_Fin) as 
(
    SELECT 1 as depth, Product_Ini, Account_Ini, Operation_Ini, Product_Fin, Account_Fin, Operation_Fin
    FROM #table_1 as a 
    UNION ALL
    SELECT b.depth + 1 AS depth, b.Product_Ini, b.Account_Ini, b.Operation_Ini, c.Product_Fin, c.Account_Fin, c.Operation_Fin
    FROM ctetable AS b JOIN #table_1 as c on c.Product_Ini = b.Product_Fin and c.Account_Ini = b.Account_Fin and c.Operation_Ini = b.Operation_Fin
)
SELECT * 
--INTO #TMP_FINAL
FROM CTETABLE 
order by Account_Ini, Operation_Ini
GO

TABLES

Answer 1

I'm afraid this may not be strictly possible with a recursive CTE.

Let's ignore table 2 for now and simply look at the simple bracket of 4 from your example in light green:

Now for these you need to consider the natural 'anchor' (see below) is row #1 ending in 852. The problem here is you cannot distinguish it naturally from the other three as they all share common characteristics.

Now, you can distinguish row four (ending in 995) from the others (using product_ini <> product_fin) however this is actually at the wrong end of your CTE chain to move up from - the anchor should be immutable and cannot inherit from the rest of the recursive CTE - by its very nature it needs to be static.

See the attached breakdown.

Now that's not to say this problem cannot be solved, it just cannot be solved with a recursive CTE.

My suggestion would be to look at a range of conditional self-joins and case statements rather than trying to be too 'smart' here. Given there are only 3 levels to work through you can 'hard code' a number of tests fairly easily and I suspect that'd be a better use of your time.