Hi, I need help creating recursive query to sum up Amounts of all ChildIDs for parent ID=1 node. Table definition and data below. This is for SQL serverless where Recursive CTE is not supported. So left join would do, The table is large and the query will be used in view so Table function is not an option either.
Recursive relation is : for ID =1 next recursions are
ID = 3 , ChildID=1
ID = 4 , ChildID=1
..then recursions for ID 3 and 4 and so on
- Same [ChildID] may belong to many [ID]s
- [Code] should be in ('MX','IN')
3.TransType<>9
My main challenge that I get dups:
-- Create the test table
CREATE TABLE #ABC (
ID INT PRIMARY KEY,
ChildID INT,
Transtype INT,
Code NVARCHAR(2),
Amount INT
);
-- Insert test records
INSERT INTO #ABC (ID, ChildID, Transtype, Code,Amount ) VALUES
(1, 2, 1, 'MX',100),
(2, 3, 1, 'IN',200),
(3, 1, 1, 'MX',300),
(4, 1, 1, 'MX',400),
(5, NULL, 1, 'MX',500),
(6, 3, 1, 'IN',600),
(7, 3, 1, 'MX',700),
(8, 3, 1, 'IN',800),
(9, NULL, 1, 'MX',900),
(10, 6, 1, 'IN',1000),
(11, 6, 1, 'MX',1100),
(12, NULL, 9, 'AX',1200),
(13, 10, 1, 'IN',1300),
(15, 10, 1, 'MX',1500),
(16, 11, 1, 'AX',1600),
(17, 10, 1, 'MX',1700),
(18, 11, 1, 'AX',1800),
(19, 20, 1, 'AX',1900) ;
--SELECT returns dups
SELECT
a1.ID a1_ID, a1.ChildID a1_ChildID, a1.Code a1_Code, a1.Amount a1_Amount,
a2.ID a2_ID, a2.ChildID a2_ChildID, a2.Code a2_Code, a2.Amount a2_Amount,
a3.ID a3_ID, a3.ChildID a3_ChildID, a3.Code a3_Code, a3.Amount a3_Amount,
a4.ID a4_ID, a4.ChildID a4_ChildID, a4.Code a4_Code, a4.Amount a4_Amount
FROM #ABC a1
LEFT JOIN #ABC a2 ON a1.ID = a2.ChildID AND a2.Transtype <> 9 and a2.Code in ('MX','IN')
LEFT JOIN #ABC a3 ON a2.ID = a3.ChildID AND a3.Transtype <> 9 and a3.Code in ('MX','IN')
LEFT JOIN #ABC a4 ON a3.ID = a4.ChildID AND a4.Transtype <> 9 and a4.Code in ('MX','IN')
WHERE a1.Transtype <> 9 and a1.Code in ('MX','IN') and a1.ID=1