Re: WHERE

On Mon, May 09, 2005 at 15:48:44 -0400,
Hrishikesh Deshmukh <hdeshmuk(at)gmail(dot)com> wrote:
> Hi All,
>
> How can one use a table created for saving the results for a query be
> used in WHERE for subsequent query!!!
>
> Step 1) create table temp as select gene from dataTable1 intersect
> select gene from dataTable2;
>
> Now temp has been created, temp has only one column which has list of
> genes and nothing else but i want to retrieve annotation for the genes
> in temp table.
>
> Is it possible to: select geneAnnotation from dataTable1 where genes =
> "gene in temp table";!!!!!

Unless you are going to reuse the table (essentailly creating a materialized
view), you should probably just do this in one statement.

SELECT geneAnnotation
FROM dataTable1
WHERE gene IN
(SELECT gene FROM dataTable2)
;

In 7.4 or later this should run pretty fast. In older versions, you probably
want to use EXISTS instead of IN. This also assumes that there are no
NULL values for gene in dataTable2.