pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Christoph Haller <ch@rodos.fzk.de>
To: philippe.lang@attiksystem.ch
Cc: pgsql-sql@postgresql.org
Subject: Re: sub-select parameter problem
Date: Thu, 04 Mar 2004 12:53:18 MET
Message-ID: <200403041153.MAA03351@rodos> (raw)
In-Reply-To: <6C0CF58A187DA5479245E0830AF84F42080222@poweredge.attiksystem.ch>

> 
> 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 
SELECT
 tableA.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 

view thread (3+ messages)

Message-ID: <200403041153.MAA03351@rodos>
Permalink:  ../200403041153.MAA03351@rodos/
Also on:    postgresql.org/message-id/200403041153.MAA03351@rodos

copy link · copy postgr.es

reply

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: ch@rodos.fzk.de, philippe.lang@attiksystem.ch
  Subject: Re: sub-select parameter problem
  In-Reply-To: <200403041153.MAA03351@rodos>

* 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