CODEWITHSUNDEEP
sqlsql-serverhierarchy
Chunghee Kim
Chunghee Kim

Reputation: 45

SQL Server hierarchy referencing and cross data referencing

This might be a stupid question, but I am not a DBA and kind of stuck with this issue. I have an application that trickles down all effects (asdf) under an applied ID (IDParent).

The data tables are setup like this:

Data Tables enter image description here

3rd Data Table

I want to write a query that when using IDChild it will reference that entry's IDParent to get the parent ID while referencing it as an IDChild. For example for the data entry starting at 116 I want to use the parent ID (124) and get 321 in T1. I want to use this to get the RandoName associated with RandoID for all of the entries that has a parent ID of 321.

Right now I am using a script something like:

Select t.[NAME]
From T2 tv
Inner join T3 t on t.RandoID = tv.RandoId
Where
tv.IDChild = T1.IDChild OR tv.IDChild = T1.IDParent

but I'm not sure how to get the whole applied hierarchy.

This would yield something like this:

Resulting Query

PS. I can not change the tables/db schema. But maybe I can add one to do all the referencing? Please tell me what you think.

EDIT I'm sorry I forgot about this other stupid table that RandoID uses which contains the name of the RandoID. I am trying to get the name of RandoID

Upvotes: 1

Views: 107

Answers (2)

Salek
Salek

Reputation: 303

I think a loop could help you.

Try this:

CREATE TABLE #t1 (IDChild Int, IDParent Int);
CREATE TABLE #t2 (RandoID NVARCHAR(10) , IDChild Int);
CREATE TABLE #RandoName (RandoID NVARCHAR(10), RandoName VARCHAR(50));

INSERT INTO #t1 VALUES (321, NULL), (123,321),(124,123),(116,124)
INSERT INTO #t2 VALUES ('asdf', 123)
INSERT INTO #RandoName VALUES ('asdf', 'something')

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 100)) [RowNum], a.IDChild a, a.IDParent b, b.IDChild c INTO #t3 FROM #t1 a
LEFT OUTER JOIN #t1 b ON b.IDParent = a.IDChild

DECLARE @rownum INT;
DECLARE cbcursor CURSOR for Select RowNum FROM #t3;
OPEN cbcursor;
Fetch Next from cbcursor into @rownum
WHILE @@FETCH_STATUS = 0
BEGIN
       UPDATE #t3
       SET c = (SELECT b from #t3 where RowNum = @rownum-1)
       WHERE RowNum = @rownum
Fetch Next from cbcursor into @rownum;
END;
Close cbcursor;
Deallocate cbcursor;

SELECT a,b,t2.RandoID, r.RandoName FROM #t3
LEFT OUTER JOIN #t2 t2 on t2.IDChild = #t3.c OR t2.IDChild = #t3.b OR t2.IDChild = #t3.a
LEFT OUTER JOIN #RandoName r on t2.RandoID = r.RandoID

This is what I get:

enter image description here

If you have any changes in your tables, like more records for T2, this script should be modified.

Upvotes: 1

KeithL
KeithL

Reputation: 5594

Using recursion:

declare @t table (IDc int , Idp int)
insert into @t
values
(321,null)
,(123,321)
,(124,123)
,(116,124)

declare @t2 table (RandoID varchar(10), IDChild int)
insert into @t2
values('asdf',123)

;with cte as
(
    select anchor = IDChild
         ,ParentOrSelf = IDc
         ,RandoID 
         ,RandomName
    from @t
    cross join (select RandoID,RandoName from @t2 t2 join @t3 t3 on t2.RandoID=t3.RandoID) crossed
    where IDc=@anchor

    union all

    select t2.IDChild
         ,IDc
         , t2.RandoID,RandomName
    from @t t
        cross join (select RandoID,RandoName from @t2 t2 join @t3 t3 on t2.RandoID=t3.RandoID) t2
        join cte on cte.ParentOrSelf = t.Idp
)

select IDc
  , cte.RandoID,cte.RandomName
from @t t
    left join cte on t.IDc = cte.ParentOrSelf

Results:

IDc RandoID
321 NULL
123 asdf
124 asdf
116 asdf

Upvotes: 0

Related Questions