Subject Re: [firebird-support] CTE Recursive left join problem
Author livius
Post date 2017-10-02T19:31:01Z
Hi,
 
this is normal sql behavior
if you do not need to implicit INNER JOIN condition then move filter to left join itself
in your situation:
instead “where mytable.id is not null” write “ AND mytable.id is not null”
 
...
select aux.n, mytable.id
from aux
   LEFT JOIN mytable on aux.n between mytable.id and mytable.id +1 and mytable.id is not null
...
 
regards,
Karol Bieniaszewski
 
Sent: Monday, October 2, 2017 7:37 PM
Subject: [firebird-support] CTE Recursive left join problem
 
  

Hello everyone
I'm having a problem, and I do not understand why.
I have a structure similar to:

with recursive
 aux as (
  select 0 as n
    from rdb$database
  union all
  select aux.n + 1
    from aux
    where aux.n <= 20
 )
 
 select aux.n, mytable.id
 from aux
   LEFT JOIN mytable on aux.n between mytable.id and mytable.id +1 
 where mytable.id is not null
 
Where the left join behaves as join.
 
Any condition that I put in the clause WHERE referred to mytable, converts the LEFT JOIN into JOIN