From: Philippe Lang <philippe.lang@attiksystem.ch>
To: pgsql-sql@postgresql.org
Subject: sub-select parameter problem
Date: Thu, 4 Mar 2004 12:20:36 +0100
Message-ID: <6C0CF58A187DA5479245E0830AF84F42080222@poweredge.attiksystem.ch> (raw)
Hello,
Imagine the following query:
-------------------------------------------
SELECT
tableA.field1,
tableA.field2,
tableB.field1,
tableB.field2,
(
SELECT tableC.field2
FROM tableC
WHERE tableC.field1 = tableB.field1 - 1;
) AS p
FROM tableA
INNER JOIN tableB
ON tableA.pk = 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,
tableB.field1,
tableB.field2,
(
SELECT tableB.field2
FROM tableB
WHERE tableB.field1 = tableB.field1 (--> from-main-select?) - 1;
) AS p
FROM tableA
INNER JOIN tableB
ON tableA.pk = 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,
tableB.field1 AS param,
tableB.field2,
(
SELECT tableB.field2
FROM tableB
WHERE tableB.field1 = param - 1; (--> does not work...)
) AS p
FROM tableA
INNER JOIN tableB
ON tableA.pk = 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,
tableB.field1,
tableB.field2,
CASE
WHEN tableB.field1 = 1 THEN
(
SELECT tableB.field2
FROM tableB
WHERE tableB.field1 = 0;
)
WHEN tableB.field1 = 2 THEN
(
SELECT tableB.field2
FROM tableB
WHERE tableB.field1 = 1;
)
WHEN tableB.field1 = 3 THEN
(
SELECT tableB.field2
FROM tableB
WHERE tableB.field1 = 2;
)
... etc...
ELSE
0
END AS p,
FROM tableA
INNER JOIN tableB
ON tableA.pk = 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
Attik System
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: philippe.lang@attiksystem.ch
Subject: Re: sub-select parameter problem
In-Reply-To: <6C0CF58A187DA5479245E0830AF84F42080222@poweredge.attiksystem.ch>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox