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
