Recursive function parent-child in t-sql
-
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
GOTABLES:
-
The code you provided executed without error and returned 24 rows. The two temp tables have identical DDL so it seems to maybe make sense to do UNION ALL and then do the recursion. To run without changing the cte add the line before:
insert #table_1 select * from #table_2;
When the code runs after the 2 tables are combined into table 1 it returns 33 rows. When insert the rows into a table maybe the ORDER BY clause could cause an error.
insert #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
GOAus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Thanks for your answer. Additionally, i want to a add a column with a flag if that row has a child, as the attached picture (final table)
-
I think you should throw out everything you've done and start over. You don't seem to know that by definition a table must have a key, so you've declared temp tables with all the little columns guaranteeing they will never have a key. There is no reference from one of your tables to the other. The use of recursion is how procedural programmers think and write code; unfortunately, SQL, is a declarative language. Adding a number postfix to a table name also implies that you don't know what First Normal Form (1NF) is, so you've created a repeating group.
Please think about how vague names like "product", "account" and "operation" are and useless for any kind of data model. These also sound like sets of things which would have an identifier, but you model them with integers as if you were still writing BASIC or FORTRAN. In fact, putting the metadata "_table" in a table name is a design flaw so bad we call it a tibble. Did you know that instead of inserting data row by row, as if you were reading punchcards in your FORTRAN program, you can insert an entire table using row constructors in a single insert statement? Besides being a lot more compact and easier to read, and has the advantage of passing a set of data to the optimizer to allow it to optimize it.
You also ought to follow at least a little minimal netiquette. We don't put screenshots on SQL forum. Now the people that are working for free for you have to transcribe data from your pictures. Should there be constraints (product_ini < product_fin), (account_ini < account_fin) and (operation_ini < operation_fin)? I am assuming that "_ini" means initial something and that "_fin" means final something. This is one of the wonderful things about SQL's ability to enforce data integrity.
I have written an entire book on trees and hierarchies in SQL. You might want to read the chapters on the nested set model, which I regard as the best way of modeling such structures in the language I spent decades of my life working on. I also think you want to throw out everything you've done and start over -- it is really that bad
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply