Обсуждение: 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