Thread: 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
Mark Harrison wrote: > > ### 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'); This won't work if your LIKE matches more than one row anyway. Try something like: SELECT id FROM p4_versions WHERE (versionof, version) IN (SELECT id,headver FROM p4_files WHERE ...) -- Richard Huxton Archonet Ltd
Mark Harrison <mh@pixar.com> writes: > I've got a query that depends upon two pieces of data from another table for > use in a where clause. > 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'); Use a row-wise comparison, viz select id from p4_versions where (versionof, version) = (select id, headver from p4_files where p4path like '%/date.txt'); regards, tom lane
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 -------
Richard Huxton wrote: > SELECT id FROM p4_versions WHERE > (versionof, version) IN (SELECT id,headver FROM p4_files WHERE ...) Ahh, just what I was looking for... Thanks All! Mark