Can’t JOIN on CTE column inside CTE, unknown column

I am trying to achieve the following…

with 
    relations as (
        select
            rm.recordId,
            rs.fieldId,
            rm.relationSettingId,
            rm.relatedRecordId
        from relationMapping rm
            left join relationSetting rs on rs.id = rm.relationSettingId
    ),
    foo as (
        select distinct 
            coalesce(rnff.fieldId, field.id)
        from field
            left join relationNameFromField rnff 
                on rnff.relationSettingId = relations.relationSettingId  -- <---- Won't work
    )

select * from foo;

I get the following error:

Unknown column 'relations.relationSettingId' in 'on clause'

Why can’t I reference the relations table?

Answer

Based on your CTE, this is just a guess, but I think you probably want:

with 
relations as (
    select
        rm.recordId,
        rs.fieldId,
        rm.relationSettingId,
        rm.relatedRecordId
    from relationMapping rm
    left join relationSetting rs
        on rs.id = rm.relationSettingId
),
foo as (
    select distinct 
        coalesce(rnff.fieldId, field.id)
    from field
    left join relations                        --add this join here
        on field.id = relations.fieldId
    left join relationNameFromField rnff 
        on rnff.relationSettingId = relations.relationSettingId
)
select * from foo;

We don’t know your table structures, so I can’t say I have the right columns for that join, but I’d say it’s a pretty educated guess.