¿Is it possible to use a table alias generated in a lateral subquery in another lateral subquery?

25 Views Asked by At

The doubt arises from a scenario like this:

  • I have a table named one_table that has many related rows in another table named another_table, via foreign key like this another_table(one_table_id) references table(id)
  • 1️⃣ I want to discard the rows of another_table that has null in column_a and do relate to the a row in one_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.

0

There are 0 best solutions below