why not a join like below (not tested)
select id
from p4_versions a
join p4_files b on (a.versionof = b.id and a.version = b.headver)
where p4path like '%/date.txt'
---------- Original Message -----------
From: Mark Harrison <mh@pixar.com>
To: Postgresql-General <pgsql-general@postgresql.org>
Sent: Thu, 27 Apr 2006 09:50:38 -0700
Subject: [GENERAL] query that needs two nested queries, is this the best way?
> I've got a query that depends upon two pieces of data from another table for
> use in a where clause.
>
> If I perform this procedurally, I can issue two sql commands (see below),
> one to get the two pieces of data to search upon (QUERY 1), and one to
> perform the query with these two pieces of data plugged in (QUERY 2).
>
> This can also be done with one query that has two subselects. However,
> this causes one redundant selection to be performed (QUERY 3).
>
> So, I have two questions:
>
> 1. Is there some way to formulate query 3 without having the redundant
> subselects?
>
> 2. Stylistically or Idiomatically, which is preferrable? I realize
> this is a pretty vague question, especially since both approaches
> produce the same answer, but I'm just looking for the emotional
> tendency of experienced SQL developers.
>
> Many TIA!
> Mark
>
> ### QUERY 1: get "id" and "headver" values for use in the next query
>
> scratch1=# select id, headver from p4_files where p4path like '%/date.txt';
> id | headver
> ----------+---------
> 60152254 | 7
>
> ### QUERY 2: use those values in the query
>
> scratch1=# select id from p4_versions where versionof=60152254 and version=7;
> id
> ----------
> 60174263
>
> ### QUERY 3: combine the two statements above by using two subselects
>
> scratch1=# select id from p4_versions where
> versionof=(select id from p4_files where p4path like '%/date.txt')
> and
> version=(select headver from p4_files where p4path like '%/date.txt');
> id
> ----------
> 60174263
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
------- End of Original Message -------