missing FROM-clause entry for table

Hi group,

I get a mysterious errormessage while executing the following query:
(I left the tabledefinitions out, since I think they don't matter. If I
am wrong I'll be happy to post them.)

SELECT DRV1.languageid, DRV1.categorypropertyid,
CPL.categorypropertylangname
FROM (SELECT L.languageid, CP.categorypropertyid FROM tbllanguage AS L,
tblcategoryproperty AS CP) AS DRV1
LEFT OUTER JOIN tblcategorypropertylang AS CPL ON
((CLP.languageid=DRV1.languageid) AND
(CPL.categorypropertyid=DRV1.categorypropertyid));

ERROR: missing FROM-clause entry for table "clp"

While this one is working just fine:

SELECT DRV1.languageid, DRV1.categorypropertyid, categorypropertylangname
FROM (SELECT L.languageid, CP.categorypropertyid FROM tbllanguage AS
L, tblcategoryproperty AS CP) AS DRV1
LEFT OUTER JOIN tblcategorypropertylang ON
((tblcategorypropertylang.languageid=DRV1.languageid) AND
(tblcategorypropertylang.categorypropertyid=DRV1.categorypropertyid));

The only difference is that I didn't use an alias CPL for
tblcategorypropertylang.
(The above queries are dynamically created by PHP based on a lot of
other, not related, things, and I would like to use the first way
because that makes sense in my current project.)

I Googled a little for the errormessage, but to no avail.
The second query is also good enough for me, but I am curious what I am
missing here.

What is causing this?

Thanks for your time.

Regards,
Erwin Moller

On 12/11/2008 18:08, Erwin Moller wrote:
> LEFT OUTER JOIN tblcategorypropertylang AS CPL ON
> ((CLP.languageid=DRV1.languageid) AND
> (CPL.categorypropertyid=DRV1.categorypropertyid));
>
> ERROR: missing FROM-clause entry for table "clp"

You've a typo in the query - the table alias is CPL, not CLP.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod(at)iol(dot)ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

----- "Erwin Moller" <erwin(at)darwine(dot)nl> wrote:

> Hi group,
>
> I get a mysterious errormessage while executing the following query:
> (I left the tabledefinitions out, since I think they don't matter. If
> I
> am wrong I'll be happy to post them.)
>
> SELECT DRV1.languageid, DRV1.categorypropertyid,
> CPL.categorypropertylangname
> FROM (SELECT L.languageid, CP.categorypropertyid FROM tbllanguage AS
> L,
> tblcategoryproperty AS CP) AS DRV1
> LEFT OUTER JOIN tblcategorypropertylang AS CPL ON
> ((CLP.languageid=DRV1.languageid) AND
^^^ instead of CPL

> (CPL.categorypropertyid=DRV1.categorypropertyid));
>
> ERROR: missing FROM-clause entry for table "clp"
>
> While this one is working just fine:
>
> SELECT DRV1.languageid, DRV1.categorypropertyid,
> categorypropertylangname
> FROM (SELECT L.languageid, CP.categorypropertyid FROM tbllanguage
> AS
> L, tblcategoryproperty AS CP) AS DRV1
> LEFT OUTER JOIN tblcategorypropertylang ON
> ((tblcategorypropertylang.languageid=DRV1.languageid) AND
> (tblcategorypropertylang.categorypropertyid=DRV1.categorypropertyid));
>
> The only difference is that I didn't use an alias CPL for
> tblcategorypropertylang.
> (The above queries are dynamically created by PHP based on a lot of
> other, not related, things, and I would like to use the first way
> because that makes sense in my current project.)
>
> I Googled a little for the errormessage, but to no avail.
> The second query is also good enough for me, but I am curious what I
> am
> missing here.
>
> What is causing this?
>
> Thanks for your time.
>
> Regards,
> Erwin Moller
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Adrian Klaver
aklaver(at)comcast(dot)net

Raymond O'Donnell schreef:
> On 12/11/2008 18:08, Erwin Moller wrote:
>
>> LEFT OUTER JOIN tblcategorypropertylang AS CPL ON
>> ((CLP.languageid=DRV1.languageid) AND
>> (CPL.categorypropertyid=DRV1.categorypropertyid));
>>
>> ERROR: missing FROM-clause entry for table "clp"
>>
>
> You've a typo in the query - the table alias is CPL, not CLP.
>
Oh my....
/me slaps head against the wall.
And I checked that query 2 times before posting here....

Maybe it is time to call it a day. ;-)

Thanks Ray! (And Adrian)

Regards,
Erwin Moller
> Ray.
>
> ------------------------------------------------------------------
> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
> rod(at)iol(dot)ie
> Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
> ------------------------------------------------------------------
>
>
>