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