Обсуждение: novice question about NOTICE:...

    Tried to search the novice archives and the engine just never
returns an answer.  This is probably a pretty common question so I
hope I'll get a quick answer.


   I have two tables  cgidat and tierdat.
    I want to select all occurrences of data in cgidat where
cgidat.state = testing and cgidat.lang = tierdat.lang and
tierdat.tier = 0

   so I did


    select oid, lang from cgidat where cgidat.state = 'testing' and
cgidat.lang = tiredat.lang and tierdat.tier = '0' order by lang;

I get the expected results but I also get
Adding missing FROM-clause entry for table "tierdat"


   Huh?

    Thanks
    Lloyd

--

                 lev@apple.com

On Wed, 3 Apr 2002, Lloyd Vancil wrote:

>     Tried to search the novice archives and the engine just never
> returns an answer.  This is probably a pretty common question so I
> hope I'll get a quick answer.
>
>
>    I have two tables  cgidat and tierdat.
>     I want to select all occurrences of data in cgidat where
> cgidat.state = testing and cgidat.lang = tierdat.lang and
> tierdat.tier = 0
>
>    so I did
>
>
>     select oid, lang from cgidat where cgidat.state = 'testing' and
> cgidat.lang = tiredat.lang and tierdat.tier = '0' order by lang;
>
> I get the expected results but I also get
> Adding missing FROM-clause entry for table "tierdat"
>
>
>    Huh?
>
>     Thanks
>     Lloyd
>
>

That was mighty nice of Postgres to fix your query -- I didn't know it
would do that.  You're doing a join from two tables, cgidat and tierdat,
so you must include them in your FROM clause:

select oid, lang from cgidat, tierdat where
cgidat.state = 'testing' and
cgidat.lang = tiredat.lang and
tierdat.tier = '0' order by lang;

or using aliases (a must when you start joining several tables):

select c.oid,c.lang from cgidat c, tierdat t where
c.state='testing' and
c.lang = t.lang and
t.tier ='0' order by c.lang


--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Doug Silver
Network Manager
Quantified Systems, Inc
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Thanks Doug.
    But then it came up with
ERROR:  Column reference "oid" is ambiguous.
and eventually
ERROR:  Column reference "lang" is ambiguous
    because -every- table has an OID and in this case both tables have
a column 'lang'


so, correctly it is
select cgidat.oid, cgidat.lang from cgidat, tierdat where
cgidat.state = 'testing' and
cgidat.lang = tiredat.lang and
tierdat.tier = '0' order by lang;
And if you dont select lang then the order by has to be qualified too.


select cgidat.oid from cgidat, tierdat where
cgidat.state = 'testing' and
cgidat.lang = tiredat.lang and
tierdat.tier = '0' order by cgidat.lang;

At 12:55 PM -0800 4/3/02, Doug Silver wrote:
>
>That was mighty nice of Postgres to fix your query -- I didn't know it
>would do that.  You're doing a join from two tables, cgidat and tierdat,
>so you must include them in your FROM clause:
>
>select oid, lang from cgidat, tierdat where
>cgidat.state = 'testing' and
>cgidat.lang = tiredat.lang and
>tierdat.tier = '0' order by lang;
>
>or using aliases (a must when you start joining several tables):
>
>select c.oid,c.lang from cgidat c, tierdat t where
>c.state='testing' and
>c.lang = t.lang and
>t.tier ='0' order by c.lang

--
searchmaster@apple.com
                 lev@apple.com

Not an expert by any means, but I think you need to include the tierdat
table in the from clause like this:

select oid, lang from cgidat, tierdat where cgidat.state = 'testing' and
cgidat.lang = tiredat.lang and tierdat.tier = '0' order by lang;

Kent

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Lloyd Vancil
Sent: Wednesday, April 03, 2002 3:37 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] novice question about NOTICE:...


    Tried to search the novice archives and the engine just never
returns an answer.  This is probably a pretty common question so I
hope I'll get a quick answer.


   I have two tables  cgidat and tierdat.
    I want to select all occurrences of data in cgidat where
cgidat.state = testing and cgidat.lang = tierdat.lang and
tierdat.tier = 0

   so I did


    select oid, lang from cgidat where cgidat.state = 'testing' and
cgidat.lang = tiredat.lang and tierdat.tier = '0' order by lang;

I get the expected results but I also get
Adding missing FROM-clause entry for table "tierdat"


   Huh?

    Thanks
    Lloyd

--

                 lev@apple.com

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org