CODEWITHSUNDEEP
sql-servert-sqlcommon-table-expression
bccn3
bccn3

Reputation: 33

Is CTE the correct way to handle recursion with two tables?

I have a query setup below, and I'm having trouble with the recursion piece. I start with a contract(s), Abc and Xyz from Table1. I take Table1.Id, groupNo and look them up in Table2, to get the contracts(s) there, then look those contracts back up in Table1, repeating the process until it eventually returns null, and capturing the last iteration. Is CTE the way to handle this? If so, could someone help with the last iteration. I tried nesting, and haven't got it to work.

Table Structure

create table Table1 (id int, groupNo int, contract varchar(3))
insert into Table1 values(33,2,'Abc')
insert into Table1 values(34,8,'Xyz')
insert into Table1 values(88,11,'123')
insert into Table1 values(89,11,'456')

create table Table2 (id int, groupNo int, contract varchar(3))
insert into Table2 values(34,8,'123')
insert into Table2 values(34,8,'456')
insert into Table2 values(89,11,'789')

Query

with myCTE (id,groupNo,contract) as
(
    select 
        t1.id
        ,t1.groupNo
        ,t2.contract 
    from Table1 t1
        inner join Table2 t2 on t1.id = t2.id and t1.groupNo = t2.groupNo
    union all
    select
        t1.id
        ,t1.groupNo
        ,c2.contract
    from myCTE c2
        inner join Table1 t1 on c2.contract = t1.contract
)

select top 10 id, groupNo, contract
from myCTE

SQL FIDDLE

Upvotes: 2

Views: 46

Answers (1)

Julien Vavasseur
Julien Vavasseur

Reputation: 3952

This is one way of doing it. Basically, I record the level of each recursion and only keep the highest level. See SQL Fiddle and query below:

declare @Table1 table(id int, groupNo int, contract varchar(3));
insert into @Table1 values(33,2,'Abc');
insert into @Table1 values(34,8,'Xyz');
insert into @Table1 values(88,11,'123');
insert into @Table1 values(89,11,'456');

declare @Table2 table(id int, groupNo int, contract varchar(3));
insert into @Table2 values(34,8,'123');
insert into @Table2 values(34,8,'456');
insert into @Table2 values(89,11,'789');



with myCTE (level, id, groupNo, contract, subcontract) as
(
    select 0, t1.id,t1.groupNo, t1.contract
        ,t2.contract 
    from @Table1 t1
        inner join @Table2 t2 on t1.id = t2.id and t1.groupNo = t2.groupNo
    union all
    select level+1, c2.id, c2.groupNo, c2.contract
        ,t2.contract
    from myCTE c2
        inner join @Table1 t1 on c2.subcontract = t1.contract
        inner join @Table2 t2 on t1.id = t2.id and t1.groupNo = t2.groupNo
)
Select c.* From myCTE as c
Inner join (select id, groupNo, contract, level = max(level) From myCTE Group by id, groupNo, contract) as m 
    On m.level = c.level and m.id = c.id and m.groupNo = c.groupNo and m.contract = c.contract
OPTION (MAXRECURSION 0);

I also added table2 to the second select. You want it to behave like the first one and it needs to get the subcontract name from table2.

Upvotes: 1

Related Questions