SQL Server 2012 - Recursive CTE with two conditions under WHERE clause

Question

#1 EDIT

I changed the AND in the WHERE clause for OR and everything worked fine. But I needed to use a JOIN in the recursive part of the CTE and the problem showed up again, because I cannot use a OUTER JOIN here. So, I changed the JOIN for an OUTER APPLY and that worked fine.

---

SQL Fiddle: http://sqlfiddle.com/#!18/9eecb/81809

I am trying to implement a recursive CTE which receives two ages and increments these ages until both of them are equal to 120. The problem is when I try to add a WHERE clause to the recursive part the predicates are completely ignored:

;with age_cte as (
    select
        26 as wife_age,
        28 as husband_age

    union all

    select
        age_cte.wife_age + 1,
        age_cte.husband_age + 1

    from age_cte

    where wife_age < 120 and husband_age < 120

) select * from age_cte;

As soon as one of the ages reaches 120 the CTE stops. In the example, when the husband age is equal to 120, the wife's age is 118 and then the calculations stop.

I know the database is obeying the logic of the query. My question is what should I do to apply the correct logic to that CTE, that is, return NULL when one age passes 120 until the other age reaches 120?

Example:

.     .
.     .
.     .
118   120
119   NULL
120   NULL

I tried using a CTE with two anchors and two recursive parts like the following from the documentation example ("H. Using multiple anchor and recursive members"):

create table age (
    wife_age int,
    husband_age int
);
insert into age values(26, 28);
;with age_cte as (
    -- first anchor
    select
        wife_age
    from age
    union
    -- second anchor
    select
        husband_age
    from age

    union all

    select
        age_cte.wife_age + 1
    from age_cte

    where wife_age < 120

    union all
    --
    select
        age_cte.husband_age + 1
    from age_cte

    where husband_age < 120

) select * from age_cte;

I'm missing something, because it gives me "Invalid column name" for the "husband_age" in the second recursive query.

I also tried this query

;with age_cte as (
    select
        26 as wife_age,
        28 as husband_age

    union all

    select
        case when age_cte.wife_age + 1 > 120 then null else age_cte.wife_age + 1 end,
        case when age_cte.husband_age + 1 > 120 then null else age_cte.husband_age + 1 end

    from age_cte

    where 120 >= case 
                     when age_cte.wife_age + 1 < age_cte.husband_age + 1 then
                         age_cte.wife_age + 1
                     else
                         age_cte.husband_age + 1
                 end


) select * from age_cte;

But either it gives an infinite loop or the age goes to 119 never reaching 120.

Answer 1

This should do what you want:

with age_cte as (
    select 26 as wife_age, 28 as husband_age
    union all
    select
        case when wife_age < 120 then wife_age + 1 end,
        case when husband_age < 120 then husband_age + 1 end
    from age_cte
    where wife_age < 120 or husband_age < 120

) 
select * from age_cte;

That is:

• you want or in the where clause of the recursive query rather than and, so the query keeps going until both ages reach 120

• you can use conditional logic in the select to produce nulls when the age exceeds 120

Demo on DB Fiddle:

wife_age | husband_age
-------: | ----------:
      26 |          28
      27 |          29
      28 |          30
      29 |          31
...
     116 |         118
     117 |         119
     118 |         120
     119 |        null
     120 |        null