Get nth level on self-referencing table

Question

I have a self-referencing table which has at max 5 levels

groupid   | parentid  | detail
--------- | --------- | ---------
Group A   | Highest   | nope 
Group B   | Group A   | i need this  
Highest   | NULL      | nope 
Group C   | Group B   | nope 
Group D   | Group C   | nope 

I have a transaction table which lookups to the groupid on the table above to retrieve the detail value where groupid = Group B. The values of the groupid on the transaction table is only between Group B to D and will never go any higher.

txnid     | groupid   | desired   | desired
--------- | --------- | --------- | ---------
1         | Group D   | Group B   | i need this
2         | Group B   | Group B   | i need this
3         | Group C   | Group B   | i need this
4         | Group B   | Group B   | i need this

How should my T-SQL script be like to attain the desired column? I can left join to the self referencing table multiple times to get until group B it's not consistent on how many time I need to join back.

Greatly appreciate any thoughts!

Answer 1

Still not clear to me how do you know which is the GROUP B, I suppose it's the record where the parent of it parent is null.

create table org(groupid char(1), parentid char(1), details varchar(20));
insert into org values
('a', null, 'nope'),('b', 'a', 'I need this'),('c', 'b', 'nope'),('d', 'c', 'nope'),('e', 'd', 'nope');

create table trans(id int, groupid char(1));
insert into trans values
(1, 'b'),(2, 'c'),(3, 'c'),(4, 'd'),(5, 'e');
GO

10 rows affected

with all_levels as
(
    select     ob.groupid groupid_b, oc.groupid groupid_c, 
               od.groupid groupid_d, oe.groupid groupid_e, 
               ob.details
    from       org ob
    inner join org oc
    on         oc.parentid = ob.groupid
    inner join org od
    on         od.parentid = oc.groupid
    inner join org oe
    on         oe.parentid = od.groupid
    where      ob.parentid is not null
) select * from all_levels;

GO

groupid_b | groupid_c | groupid_d | groupid_e | details    
:-------- | :-------- | :-------- | :-------- | :----------
b         | c         | d         | e         | I need this

--= build a 4 levels row
with all_levels as
(
    select     ob.groupid groupid_b, oc.groupid groupid_c, 
               od.groupid groupid_d, oe.groupid groupid_e, 
               ob.details
    from       org ob
    inner join org oc
    on         oc.parentid = ob.groupid
    inner join org od
    on         od.parentid = oc.groupid
    inner join org oe
    on         oe.parentid = od.groupid
    where      ob.parentid is not null
) 
    --= no matter what groupid returns b group details
    , only_b as
    (
        select groupid_b as groupid, groupid_b, details from all_levels
        union all
        select groupid_c as groupid, groupid_b, details from all_levels
        union all
        select groupid_d as groupid, groupid_b, details from all_levels
        union all
        select groupid_e as groupid, groupid_b, details from all_levels
    )
    --= join with transactions table
    select     id, t.groupid, groupid_b, ob.details
    from       trans t
    inner join only_b ob
    on         ob.groupid = t.groupid;
GO

id | groupid | groupid_b | details    
-: | :------ | :-------- | :----------
 1 | b       | b         | I need this
 2 | c       | b         | I need this
 3 | c       | b         | I need this
 4 | d       | b         | I need this
 5 | e       | b         | I need this

dbfiddle here

You can deal with a recursive function too, but I don't believe it can be better on terms of performance.

create function findDetails(@groupid char(1))
returns varchar(100)
as
begin
    declare @parentid char(1) = '1';
    declare @next_parentid char(1) = '1';
    declare @details varchar(100) = '';

    while @next_parentid is not null
    begin
        select     @details = org.details, @parentid = org.parentid, @next_parentid = op.parentid
        from       org
        inner join org op
        on         op.groupid = org.parentid
        where      org.groupid = @groupid

        set @groupid = @parentid;
    end

    return @details;
end
GO

✓

select id, groupid, dbo.findDetails(groupid) as details_b
from   trans;
GO

id | groupid | details_b  
-: | :------ | :----------
 1 | b       | I need this
 2 | c       | I need this
 3 | c       | I need this
 4 | d       | I need this
 5 | e       | I need this

dbfiddle here