Re: WHERE

Hi,

I have two tables with genes and its annotation and a bunch of
parameters, i wanted to know what are the genes common to these two
tables, so i wrote this query

create table temp as select gene from dataTable1 intersect
select gene from dataTable2;

The resulting table has only genes list (one column).

Now i want to retrieve gene annotation from datatable1, so how i guess
one has to write a query select geneAnnotation from dataTable1 where
genes= " temp table";

Would join do the trick!!!

Thanks,
Hrishi




On 5/9/05, Dann Corbit <DCorbit@connx.com> wrote:
> Temp tables go away after the transaction completes.
> Perhaps you want a permanent table, dropped later with "drop table".
> Either that, or you may want to bracket the entire sequence in
> begin/end.
>
> I imagine that what you want to accomplish can be done.
>
> But your pseudo-code is very vague.
>
> Give the actual SQL to define the tables involved in your query.
>
> Then, describe carefully exactly what you want to accomplish.
>
> Then someone can give you a correct answer of exactly how to accomplish
> it.
>
> > -----Original Message-----
> > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> > owner@postgresql.org] On Behalf Of Hrishikesh Deshmukh
> > Sent: Monday, May 09, 2005 12:49 PM
> > To: Postgresql-General
> > Subject: [GENERAL] WHERE
> >
> > 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";!!!!!
> >
> > Or am i here in some serious soup!!! :((
> >
> > Thanks,
> > Hrishi
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 8: explain analyze is your friend
>