Thread: sub-select parameter problem

sub-select parameter problem

From

"Philippe Lang"

Date:

04 March 2004, 10:20:46

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

Re: sub-select parameter problem

From

Richard Huxton

Date:

04 March 2004, 10:52:05

On Thursday 04 March 2004 11:20, Philippe Lang wrote:
[working query]

> It works fine.

Excellent! :-)
Oh - there's more :-(

> 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:

You're along the right lines, except I think you want to alias the table not 
the column.

...
( SELECT x.field2 FROM tableB AS x WHERE x.field1 = tableB.field1 - 1
)
...

HTH
--  Richard Huxton Archonet Ltd

Re: sub-select parameter problem

From

Christoph Haller

Date:

04 March 2004, 10:54:34

> 
> 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 
SELECTtableA.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