Search Postgresql Archives

Re: WHERE

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx> 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@xxxxxxxxxxxxxx [mailto:pgsql-general-
> > owner@xxxxxxxxxxxxxx] On Behalf Of Hrishikesh Deshmukh
> > Sent: Monday, May 09, 2005 12:49 PM
> > To: Postgresql-General
> > Subject:  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
>

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux