September 19, 2016 at 11:25 pm
Hi, I have two tables. One group contains data like this
ChildNode ParentNode
1 null
2 null
...
11 1
12 1
13 2
...
25 11
Second table contains data like this
Node BSid
1 abcd01
2 ajkl01
3 bdfg01
11 ad122
13 ch512
...
I want to write a simple code for a node id and get the corresponding BSid from second table. However if the node is not present in second table like 12 then it should take the BSid of its highest parent node.
September 20, 2016 at 12:21 am
This should be of help..
DECLARE @FirstTable TABLE
(
ChildNode INT,
ParentNode INT
)
DECLARE @SecondTable TABLE
(
Node INT,
BSid VARCHAR(20)
)
INSERT @FirstTable( ChildNode, ParentNode )
SELECT 1, NULL UNION ALL
SELECT 2, NULL UNION ALL
SELECT 11, 1 UNION ALL
SELECT 12, 1 UNION ALL
SELECT 13, 2 UNION ALL
SELECT 25, 11
INSERT @SecondTable( Node, BSid )
SELECT 1, 'abcd01' UNION ALL
SELECT 2, 'ajkl01' UNION ALL
SELECT 3, 'bdfg01' UNION ALL
SELECT 11, 'ad122' UNION ALL
SELECT 13, 'ch512'
; WITH cte AS
(
SELECT F.ChildNode, S.BSid
FROM @FirstTable AS F
INNER JOIN @SecondTable AS S
ON F.ChildNode = S.Node
WHERE F.ParentNode IS NULL
UNION ALL
SELECT FT.ChildNode, C.BSid
FROM cte AS C
INNER JOIN @FirstTable AS FT
ON C.ChildNode = FT.ParentNode
)
SELECT CT.ChildNode, ISNULL( S.BSid, CT.BSid ) AS BSid
FROM cte AS CT
LEFT OUTER JOIN @SecondTable AS S
ON CT.ChildNode = S.Node;
Please note how I have created sample data and DDL of the tables involved
If you provide us the data in this format along with expected results you will get quick tested answers
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 28, 2016 at 1:18 am
Apologies for the late reply but the query works like charm.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply