Re: WHERE

If I understand correctly, you are not creating a temp table, but
(rather) a table called 'temp.'

What you want to accomplish is still not entirely clear to me.

Do you wish to "embellish" or "enhance" the 'temp' table data with
description data?  If so, then you want an outer join.

Do you want to throw away all those rows from table 'temp' which do not
have descriptions and show only those for which there is an entry and
for which a description exists also?  If that is the case then you want
an inner join.

Please show the actual schema for the two tables (names, columns,
indexes, etc.) that will participate in the join.

I think for sure what you want to happen can be performed, but the exact
table definitions will be more helpful than generalities to create an
accurate solution.

If you do not know how to list the schema for tables, try using the
PGAdmin III tool or something similar.

> -----Original Message-----
> From: Hrishikesh Deshmukh [mailto:hdeshmuk@gmail.com]
> Sent: Monday, May 09, 2005 1:05 PM
> To: Dann Corbit
> Cc: Postgresql-General
> Subject: Re: [GENERAL] 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
> >