WHERE

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)---------------------------

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

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 =

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)---------------------------