Re: novice question about NOTICE:...

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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~