From: Tom Lane <tgl@sss.pgh.pa.us>
To: Sam Stearns <sam.stearns@dat.com>
cc: pgsql-sql@lists.postgresql.org, Peter Garza <peter.garza@dat.com>,
        Henry Ashu <henry.ashu@dat.com>
Subject: Re: invalid reference to FROM-clause entry for table
In-reply-to: 
 <CAN6TVj=Kn0b1a5NDujb8rgWG8jDJhGLMWwCW-ZgQEuM=vND=dA@mail.gmail.com>
References: 
 <CAN6TVj=Kn0b1a5NDujb8rgWG8jDJhGLMWwCW-ZgQEuM=vND=dA@mail.gmail.com>
Comments: In-reply-to Sam Stearns <sam.stearns@dat.com>
	message dated "Thu, 03 Oct 2024 15:14:46 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <472954.1727994092.1@sss.pgh.pa.us>
Date: Thu, 03 Oct 2024 18:21:32 -0400
Message-ID: <472955.1727994092@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/472955.1727994092%40sss.pgh.pa.us>
Precedence: bulk

Sam Stearns <sam.stearns@dat.com> writes:
> This one is really doing my head in:
>   ...
>   FROM (pud_fme_data d
>         inner join csbuser u on (u.userid=d.user_id)
>         inner join office o on (u.officeid=o.officeid)
>         left outer join login l on (l.userid=u.userid) ) alias6;

> ERROR:  invalid reference to FROM-clause entry for table "o"
> LINE 2:     o.crmaccountid AS crm_account_id,
>             ^
> DETAIL:  There is an entry for table "o", but it cannot be referenced from
> this part of the query.

IIRC, the join alias "alias6" hides any table aliases inside it.
Leave that off.  Or reference the column as "alias6.crmaccountid".
(This way might require fooling around with column aliases so that
crmaccountid is a unique column name within that scope.)

			regards, tom lane