Content-Type: text/plain;
  charset="utf-8"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: Wei Weng <wweng@kencast.com>, pgsql-sql@postgresql.org
Subject: Re: How do you write this query?
Date: Thu, 31 Oct 2002 19:09:11 +0000
User-Agent: KMail/1.4.3
References: <1036088486.13986.19.camel@Monet>
In-Reply-To: <1036088486.13986.19.camel@Monet>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200210311909.11341.dev@archonet.com>

On Thursday 31 Oct 2002 6:21 pm, Wei Weng wrote:
>  data | data1 | data2
> ------+-------+-------
>     1 | foo   | bar
>     2 | greg  | bar
>     3 | pooh  | bar
>     4 | dah   | peng
>
> I need a query that returns me the "data1" that satisfies the logic of
> the following pseudo code:
>
> 1: select data2 into @out from test where data1 =3D 'pooh'
> 2: select data1 from test where data2 =3D @out and data =3D 3

The most literal would be something like:

SELECT t1.data1 FROM test t1
WHERE t1.data=3D3 AND t1.data2 IN
    (SELECT t2.data2=20
    FROM test t2=20
    WHERE t2.data1=3D'pooh')

You can probably get away without the t1/t2 stuff but that should make thin=
gs=20
clear.

Since Postgresql isn't very good at optimising IN, you might want to rewrit=
e=20
it as an EXISTS query instead - see the manuals and mailing list archives f=
or=20
details.

HTH
--=20
  Richard Huxton