I suggest the best place to put your tests is in the WHERE clause. They are
out of place in the JOIN clauses, even though this (apparently) works as you
expect, because they do not represent a joining of fields. Also, easier to
read and understand in the WHERE clause.
On joins in general: there are plenty of kinds, but in real life inner or
outer (left or right: all the same) joins will be all you want 99% of the
time. Read up on what those do, and you'll be in good shape.
> -----Original Message-----
> From: Andreas Joseph Krogh [SMTP:andreak@officenet.no]
> Sent: Wednesday, August 08, 2001 9:41 AM
> To: pgsql-sql@postgresql.org
> Subject: Re: RE: Referencing named attribute in where clause doesn't
> work with7.1.2?
>
> Jeff Eckermann wrote:
> >
> > The WHERE clause is evaluated before your SELECT list is determined, so
> the
> > aliased value cannot be used.
> > You can put further NOT NULL tests into the subqueries to make sure that
> > null values are not returned.
> > Question: why not just join the tables explicitly?
>
> :-) Because I'm not too familiar with joins.
>
> > The more usual SQL
> > approach would be something like:
> >
> > SELECT article.title_text_key, on_text.text_value AS title_text_value
> > FROM article INNER JOIN (on_text INNER JOIN (code INNER JOIN code_group
> ON
> > code.code_group_id = code.id) ON on_text.lang_id = code.id) ON
> > article.title_text_key = on_text.text_key
> > WHERE on_text.text_value IS NOT NULL;
> >
> > or whatever other tests you want. In this case, you can easily
> reference
> > the fields by name.
>
> The problem with the query above is that it doesn't include my
> "code.code_key='lang.NO'" test.
>
> I rephrased the query as follows:
>
> SELECT article.title_text_key, on_text.text_value AS title_text_value
> FROM article INNER JOIN (on_text INNER JOIN (code INNER JOIN code_group
> ON
> code.code_group_id = code_group.id) ON on_text.lang_id = code.id AND
> code_group.description = 'lang' AND code.code_key = 'lang.NO') ON
> article.title_text_key = on_text.text_key
> WHERE on_text.text_value IS NOT NULL;
>
> And now it works!
>
> Thank you for helping me out.
>
> --
> Andreas Joseph Krogh <andreak@officenet.no>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org