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

[Richard Huxton <dev@xxxxxxxxxxxx>]

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