Обсуждение: 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
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
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 >
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 > >
On 5/9/05, Hrishikesh Deshmukh <hdeshmuk@gmail.com> wrote: > 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"; > select * from datatable1 where genes in (select gene from temp); Actually, i don't like the name "temp" for a table it seems error prone. -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;)
On Mon, May 09, 2005 at 15:48:44 -0400,
Hrishikesh Deshmukh <hdeshmuk@gmail.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.
On Mon, May 09, 2005 at 12:58:06PM -0700, Dann Corbit wrote:
> Temp tables go away after the transaction completes.
Connection, actually, no?
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun
Right. I should have said "session". > -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of Andrew Sullivan > Sent: Friday, May 13, 2005 11:47 AM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] WHERE > > On Mon, May 09, 2005 at 12:58:06PM -0700, Dann Corbit wrote: > > Temp tables go away after the transaction completes. > > Connection, actually, no? > > A > > -- > Andrew Sullivan | ajs@crankycanuck.ca > The fact that technology doesn't work is no bar to success in the > marketplace. > --Philip Greenspun > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings