X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 79DA9D1C96F
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  4 Mar 2004 11:54:32 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 30259-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  4 Mar 2004 07:54:22 -0400 (AST)
Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52])
	by svr1.postgresql.org (Postfix) with SMTP id 87AA8D1C94B
	for <pgsql-sql@postgresql.org>; Thu,  4 Mar 2004 07:54:19 -0400 (AST)
Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ;
 Thu Mar 04 12:53:19 2004 +0100
Received: œby rodos.fzk.de with ESMTP (8.8.6 (PHNE_17135)/8.7.1) id MAA26755; Thu, 4 Mar 2004 12:53:18 +0100 (MET)
From: Christoph Haller <ch@rodos.fzk.de>
Message-Id: <200403041153.MAA03351@rodos>
Subject: Re: sub-select parameter problem
To: philippe.lang@attiksystem.ch (Philippe Lang)
Date: Thu, 04 Mar 2004 12:53:18 MET
Cc: pgsql-sql@postgresql.org
In-Reply-To: 
 <6C0CF58A187DA5479245E0830AF84F42080222@poweredge.attiksystem.ch>;
	from "Philippe Lang" at Mar 4, 104 12:23 (noon)
X-Mailer: Elm [revision: 212.4]
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Amavis-Alert: BAD HEADER Non-encoded 8-bit data (char 9C hex) in message
	header 'Received' Received: \234by rodos.fzk.de...            ^
X-Archive-Number: 200403/35
X-Sequence-Number: 17134

> 
> Hello,
> 
> Imagine the following query:
> 
> -------------------------------------------
> SELECT
> 
>   tableA.field1,
>   tableA.field2,
> =20=20
>   tableB.field1,
>   tableB.field2,
> 
>   (
>     SELECT tableC.field2
>     FROM tableC
>     WHERE tableC.field1 =3D tableB.field1 - 1;
>   ) AS p
> 
> FROM tableA
> INNER JOIN tableB
> ON tableA.pk =3D tableB.FK;
> -------------------------------------------
> 
> It works fine.
> 
> Now, I need to do something else: the parameter of my sub-select is also
> a member of the table I'm selecting.
> 
> -------------------------------------------
> SELECT
> 
>   tableA.field1,
>   tableA.field2,
> =20=20
>   tableB.field1,
>   tableB.field2,
> 
>   (
>     SELECT tableB.field2
>     FROM tableB
>     WHERE tableB.field1 =3D tableB.field1 (--> from-main-select?) - 1;
>   ) AS p
> 
> FROM tableA
> INNER JOIN tableB
> ON tableA.pk =3D tableB.FK;
> -------------------------------------------
> 
> How can I refer to the tableB.field1 parameter from the main query? I've
> tried to do something like this, but without success:
> 
[snip]
If the tableC example works fine - this should do too 
SELECT
 tableA.field1,
 tableA.field2,
 tableB.field1,
 tableB.field2,
 ( SELECT tB.field2
   FROM tableB tB
   WHERE tB.field1 = tableB.field1 - 1;
 ) AS p
FROM tableA
INNER JOIN tableB
ON tableA.pk = tableB.FK;

HTH 

Regards, Christoph