February 24, 2020 at 1:52 pm
I have 3 tables, I need recursive query
Table3 is recursive join with Table1
I have attached the data sheet and required output with this post. It should be dynamic and recursive.
Thanks in Advance 🙂
Sorry I have updated Sample data
February 24, 2020 at 2:51 pm
Looking at that data set and sample output, I am confused on a few things. First, why would this need to be dynamic? Second, why would this need to be recursive? Could something like this work (note - this may not be the most efficient code, but works for your example):
SELECT
[T1].[col1]
, [T2].[col6]
, [T3].[col9]
, [T3].[col6]
FROMtable1 AS [T1]
JOINtable2 AS [T2]
ON [T1].[col1] = [T2].[col4]
JOIN
(
SELECT
[T3].[col9]
, [T2_2].[col6]
, [T3].[col7]
FROMtable3 AS [T3]
JOINtable2 AS [T2_2]
ON [T3].[col9] = [T2_2].[col4]
)AS [T3]
ON [T3].[col7] = [T2].[col6];
It isn't recursive or dynamic, but not entirely sure which parts are needed to be recursive or dynamic.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 24, 2020 at 3:24 pm
Hi @Mr. Brain Gale,
Thanks a lot for your response.
Sorry for the confusion.
I have updated data.
Based on Table3 col9 need to recursive to Table1 col1
February 24, 2020 at 5:58 pm
Still not seeing the "recursion" that is required here. With the updated data, does this not work:
SELECT
[T1].[col11]
, [T1].[col1]
, [T1].[col2]
, [T2].[col5]
, [T2].[col6]
, [T3].[col9]
, [T3].[col2]
FROMtable1 AS [T1]
JOINtable2 AS [T2]
ON [T1].[col1] = [T2].[col4]
FULL OUTER JOIN
(
SELECT
[T3].[col9]
, [T3].[col7]
, [T1_2].[col2]
FROMtable3 AS [T3]
JOINtable1 AS [T1_2]
ON [T3].[col9] = [T1_2].[col1]
)AS [T3]
ON [T3].[col7] = [T2].[col6];
Rather than using recursion, I am just grabbing it as a nested SELECT. As long as a column from one table can be linked to a column from another table, using JOINs and/or nested SELECTs should be able to provide you with whatever you are looking for. Presuming you have something you can JOIN on. In your case, my assumption is:
Table1 col1 can join (likely foreign key) over to Table2 col4. Table3 col9 can join over to Table1 col1. BUT Table3 col9 cannot join back to table2 col4.
One way of thinking of the above is 2 different SELECT queries. The first query is getting Table1 and Table2 data. The second SELECT (the nested SELECT) is getting Table3 and Table1 data. Then it just joins them all together. By making the nested SELECT a full outer join, it will include things where no result comes back from that data set (ie where col9 and col2 come back as NULL). By making the join from Table1 and Table2 an inner join (the default when nothing is specified), we are only returning rows where there is a match between Table1 and Table2.
Implementing something like this in C# (or any other .NET language) to combine 3 datatables into a single datatable output, I can see where recursion could be beneficial for it. But in SQL Server, I do not see how recursion would help.
I also do not see a nice way (or need) to do something like this in dynamic SQL. In the event that Table1, Table2, and Table3 (possibly more) are unknown at the time of running the query AND the columns are unknown at the time of running, auto-generating and executing TSQL to build up the output feels like a lot of work and a lot of risk and potential for a lot of useless results.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply