sub-select parameter problem

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