How to join two equivalent tables which are the result of the previous recursive select in SQL Server -

good day everyone! firstly, i'm sorry poor english. well, i've got question can read in title of message.

sql server returns message(error 253) when i'm trying select necessary data.

translate "recursive element cte (which name 'recurse' - note) has multiple reference in cte.

how can solve problem?

can advice me how join 2 tables (with 2 columns(for example : , b) result of previous recursive select (i'm writing same select, iteration of if)

    recurse (who_acts,on_whom_influence)  ( -------------------------------------------first select select  distinct interface_1.robot_name who_acts,interface_2.robot_name on_whom_influence interface interface_1,interface interface_2     (interface_1.number in (                                  select  input_interface.source                                         input_interface                                 )             , interface_2.number in (                                         select input_interface.number                                          input_interface                                         (input_interface.source=interface_1.number )                                      )           )  -------------------------------------------recursive part    union select rec1.who_acts,rec1.on_whom_influence recurse rec1 inner join  (select rec2.who_acts,rec2.on_whom_influence recurse rec2) p on (1=1) ) select  * recurse 

the problem in recurse cte.the connecting condition not simple, have no influence on problem. can type working code in comments

here's dummy table

create table tbl1 ( int, b int ); insert tbl1 select 1,2; insert tbl1 select 11,12; insert tbl1 select 2,3; insert tbl1 select 4,5; 

and similar query yours

with cte (   select a,b tbl1   union   select x.a,x.b cte x join cte y on x.a=y.a+1 ) select * cte; 

the error:

recursive member of common table expression 'cte' has multiple recursive references.: cte ( select a,b tbl1 union select x.a,x.b cte x join cte y on x.a=y.a+1 ) select * cte

basically, error exactly says. cannot have recursive cte appear more once in recursive section. above, see cte aliased both x , y. there various reasons limitation, such fact ctes recursed depth-first , not generation-by-generation.

---

what should think why need more once. recursive portion doesn't make sense.

select rec1.who_acts,rec1.on_whom_influence recurse rec1 inner join  (   select rec2.who_acts,rec2.on_whom_influence     recurse rec2) p on (1=1) 

on surface, following true if recurse real table (non-cte):

1. the number of rows generated count(recurse [rec1]) x count(recurse [rec2]).
2. the rows in recurse (rec1) each replicated per row in recurse, hence #1
3. columns rec2 never used. rec2 serves multiply

if permitted run, recursive portion of query keep quadratically increasing number of rows , never finish.