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@xxxxxxxxx> To: Postgresql-General <pgsql-general@xxxxxxxxxxxxxx> 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 -------
