Re: sub-select parameter problem - Mailing list pgsql-sql

From Christoph Haller
Subject Re: sub-select parameter problem
Date
Msg-id 200403041153.MAA03351@rodos
Whole thread Raw
In response to sub-select parameter problem  ("Philippe Lang" <philippe.lang@attiksystem.ch>)
List pgsql-sql
> 
> 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 



pgsql-sql by date:

Previous
From: Richard Huxton
Date:
Subject: Re: sub-select parameter problem
Next
From: "Rodrigo Sakai"
Date:
Subject: How to avoid (stop) a endless loop in a trigger