Skip site navigation (1) Skip section navigation (2)

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

From: "Jim Buttafuoco" <jim(at)contactbda(dot)com>
To: Mark Harrison <mh(at)pixar(dot)com>, Postgresql-General <pgsql-general(at)postgresql(dot)org>
Subject: Re: query that needs two nested queries, is this the best way?
Date: 2006-04-27 17:40:02
Message-ID: 20060427173804.M86590@contactbda.com (view raw or flat)
Thread:
Lists: pgsql-general
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(at)pixar(dot)com>
To: Postgresql-General <pgsql-general(at)postgresql(dot)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 -------


In response to

pgsql-general by date

Next:From: juan jose dominguezDate: 2006-04-27 17:56:41
Subject: CALCULAR EDAD!
Previous:From: Tom LaneDate: 2006-04-27 17:06:29
Subject: Re: query that needs two nested queries, is this the best way?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group