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 DE782D1B4B8
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  4 Mar 2004 11:20:44 +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 27213-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  4 Mar 2004 07:20:38 -0400 (AST)
Received: from poweredge.attiksystem.ch (adsl-soholight-4-c59-p029.vtx.ch
	[212.147.59.29])
	by svr1.postgresql.org (Postfix) with ESMTP id 43729D1B4B0
	for <pgsql-sql@postgresql.org>; Thu,  4 Mar 2004 07:20:35 -0400 (AST)
content-class: urn:content-classes:message
Subject: sub-select parameter problem
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Date: Thu, 4 Mar 2004 12:20:36 +0100
Message-ID: <6C0CF58A187DA5479245E0830AF84F42080222@poweredge.attiksystem.ch>
X-MimeOLE: Produced By Microsoft Exchange V6.0.6487.1
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: sub-select parameter problem
Thread-Index: AcQB2vH40HIhuGQVTkSEP1lc0u1TXQ==
From: "Philippe Lang" <philippe.lang@attiksystem.ch>
To: <pgsql-sql@postgresql.org>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200403/33
X-Sequence-Number: 17132

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:

-------------------------------------------
SELECT

  tableA.field1,
  tableA.field2,
=20=20
  tableB.field1 AS param,
  tableB.field2,

  (
    SELECT tableB.field2
    FROM tableB
    WHERE tableB.field1 =3D param - 1;    (--> does not work...)
  ) AS p

FROM tableA
INNER JOIN tableB
ON tableA.pk =3D tableB.FK;
-------------------------------------------

The only workaround I found is to use CASE... WHEN, but this is not
really robust, nor elegant.

-------------------------------------------
SELECT

  tableA.field1,
  tableA.field2,
=20=20
  tableB.field1,
  tableB.field2,

  CASE

  WHEN tableB.field1 =3D 1 THEN
  (
    SELECT tableB.field2
    FROM tableB
    WHERE tableB.field1 =3D 0;
  )

  WHEN tableB.field1 =3D 2 THEN
  (
    SELECT tableB.field2
    FROM tableB
    WHERE tableB.field1 =3D 1;
  )

  WHEN tableB.field1 =3D 3 THEN
  (
    SELECT tableB.field2
    FROM tableB
    WHERE tableB.field1 =3D 2;
  )

  ... etc...

  ELSE
  0

  END AS p,

FROM tableA
INNER JOIN tableB
ON tableA.pk =3D tableB.FK;
-------------------------------------------

In my particular application, this is almost acceptable, but I'm sure
there is a better way to do that...


Thanks for your help! (And for reading, by the way!)


-------------------------------
Philippe Lang=20
Attik System