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

From: Mark Harrison <mh(at)pixar(dot)com>
To: Postgresql-General <pgsql-general(at)postgresql(dot)org>
Subject: query that needs two nested queries, is this the best way?
Date: 2006-04-27 16:50:38
Message-ID: 4450F65E.7020307@pixar.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Richard Huxton 2006-04-27 17:00:20 Re: query that needs two nested queries, is this the best
Previous Message vka 2006-04-27 16:33:39 Postmaster process on port 10000