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