query that needs two nested queries, is this the best way?

Поиск
Список
Период
Сортировка
От Mark Harrison
Тема query that needs two nested queries, is this the best way?
Дата
Msg-id 4450F65E.7020307@pixar.com
обсуждение исходный текст
Ответы Re: query that needs two nested queries, is this the best  (Richard Huxton <dev@archonet.com>)
Re: query that needs two nested queries, is this the best way?  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: query that needs two nested queries, is this the best way?  ("Jim Buttafuoco" <jim@contactbda.com>)
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: Richard Huxton
Дата:
Сообщение: Re: problem with unique text column
Следующее
От: Richard Huxton
Дата:
Сообщение: Re: query that needs two nested queries, is this the best