Received: from www.nettverk.no (pc237.kf.kommorg.no [193.212.171.237] (may be forged)) by postgresql.org (8.11.3/8.11.4) with ESMTP id f78EfW653525 for ; Wed, 8 Aug 2001 10:41:32 -0400 (EDT) (envelope-from andreak@officenet.no) Received: from localhost.localdomain ([127.0.0.1] helo=officenet.no) by www.nettverk.no with esmtp (Exim 3.32 #2) id 15UUWA-0006ts-00 for pgsql-sql@postgresql.org; Wed, 08 Aug 2001 16:41:06 +0200 Message-ID: <3B714F82.D8912EEE@officenet.no> Date: Wed, 08 Aug 2001 16:41:06 +0200 From: Andreas Joseph Krogh X-Mailer: Mozilla 4.77 [en] (X11; U; Linux 2.2.17-21mdksecure i686) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: RE: Referencing named attribute in where clause doesn't work with7.1.2? References: <08CD1781F85AD4118E0800A0C9B8580B094B8D@NEZU> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200108/123 X-Sequence-Number: 4344 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