The doubt arises from a scenario like this:
- I have a table named
one_tablethat has many related rows in another table namedanother_table, via foreign key like thisanother_table(one_table_id) references table(id) - 1️⃣ I want to discard the rows of
another_tablethat has null incolumn_aand do relate to the a row inone_table - 2️⃣ over the rows not discarted in the first step I want to have rows that fulfills a given condition (
column_b = 'a') - 3️⃣ over the rows not discarted in the first step I want to have rows that fulfills another given condition (
column_b = 'b')
So I write a query like this:
select *
from one_table t
cross join lateral (
select *
from another_table at
where t.id = at.one_table_id and at.column_a is null
) as filtered_corelated_table -- 1️⃣
cross join lateral (
select *
from filtered_corelated_table fct
where fct.column_b = 'a'
) as filtered_1 -- 2️⃣
cross join lateral (
select *
from filtered_corelated_table fct
where fct.column_b = 'b'
) as filtered_2 -- 3️⃣
And i'm getting an error that says that relation "filtered_corelated_table" doesn't exists.
TD;DR:
Clearly the symbol filtered_corelated_table is not reachable within other laterals subqueries... but I do wonder if there is a way to archieve it and if it is not possible AND IF it is not possible to do it, then I would like to ask... ¿ why is not possible? I thought that table alias defined in lateral subqueries are available in other joins... i guess I was wrong...
I solved problem using another query.
As always, thanks in advance.