pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSELECT FROM t1 WHERE id IN (SELECT id FROM t2 UNION SELECT id FROM t3)
5+ messages / 4 participants
[nested] [flat]
* SELECT FROM t1 WHERE id IN (SELECT id FROM t2 UNION SELECT id FROM t3)
@ 2000-11-10 16:25 Christophe Boyanique <cboyanique@formanet.be>
0 siblings, 2 replies; 5+ messages in thread
From: Christophe Boyanique @ 2000-11-10 16:25 UTC (permalink / raw)
To: pgsql-sql
Hello,
I've got a problem with UNION and SELECT IN
I have a first table:
CREATE TABLE products
(
idProduct INT4,
name VARCHAR(32)
);
and two others tables:
CREATE TABLE orders
(
id INT4,
ts TIMESTAMP
);
CREATE TABLE preorders
(
id INT4,
ts TIMESTAMP
);
(I reduced the tables to be as simple as possible)
I want to retrieve id of products from the tables orders and preorders;
and order it by the name of the product. So I tried:
SELECT idProduct FROM products
WHERE idProduct IN
(SELECT id FROM orders WHERE ts>'2000-10-01 17:04:00'
UNION SELECT id FROM preorders WHERE ts>'2000-10-01 17:04:00')
ORDER by name;
and I've got a parse error near UNION or SELECT depending of the
presence
of () between the SELECTs.
I definitively need some help to solve this problem :-|
I asked to a friend to test it with Oracle and that seems to work so I
really don't know what to do...
Christophe.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SELECT FROM t1 WHERE id IN (SELECT id FROM t2 UNION SELECT id FROM t3)
@ 2000-11-10 16:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Christophe Boyanique <cboyanique@formanet.be>
1 sibling, 2 replies; 5+ messages in thread
From: Tom Lane @ 2000-11-10 16:35 UTC (permalink / raw)
To: Christophe Boyanique <cboyanique@formanet.be>; +Cc: pgsql-sql
Christophe Boyanique <cboyanique@formanet.be> writes:
> SELECT idProduct FROM products
> WHERE idProduct IN
> (SELECT id FROM orders WHERE ts>'2000-10-01 17:04:00'
> UNION SELECT id FROM preorders WHERE ts>'2000-10-01 17:04:00')
> ORDER by name;
> and I've got a parse error near UNION or SELECT depending of the
Current releases don't handle UNION in sub-selects. 7.1 will.
In the meantime, consider doing the UNION into a temp table and then
using that for the IN operator.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SELECT FROM t1 WHERE id IN (SELECT id FROM t2 UNION SELECT id FROM t3)
@ 2000-11-10 16:45 Christophe Boyanique <cboyanique@formanet.be>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Christophe Boyanique @ 2000-11-10 16:45 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Tom Lane wrote :
> Current releases don't handle UNION in sub-selects. 7.1 will.
Thanks very much for this "fast as speed light" answer !
Is there an idea about the 7.1 release date ? Just to know if this is
will be in weeks, months or years...
Christophe.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SELECT FROM t1 WHERE id IN (SELECT id FROM t2 UNION SELECT id FROM t3)
@ 2000-11-10 18:10 Tomas Berndtsson <tomas@nocrew.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Tomas Berndtsson @ 2000-11-10 18:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Christophe Boyanique <cboyanique@formanet.be>; pgsql-sql
Tom Lane <tgl@sss.pgh.pa.us> writes:
> Christophe Boyanique <cboyanique@formanet.be> writes:
> > SELECT idProduct FROM products
> > WHERE idProduct IN
> > (SELECT id FROM orders WHERE ts>'2000-10-01 17:04:00'
> > UNION SELECT id FROM preorders WHERE ts>'2000-10-01 17:04:00')
> > ORDER by name;
>
> > and I've got a parse error near UNION or SELECT depending of the
>
> Current releases don't handle UNION in sub-selects. 7.1 will.
>
> In the meantime, consider doing the UNION into a temp table and then
> using that for the IN operator.
Is it possible to create a view with UNION and use that in the
sub-select?
Tomas
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: SELECT FROM t1 WHERE id IN (SELECT id FROM t2 UNION SELECT id FROM t3)
@ 2000-11-17 00:02 Gary Farmer <farmer@arlut.utexas.edu>
parent: Christophe Boyanique <cboyanique@formanet.be>
1 sibling, 0 replies; 5+ messages in thread
From: Gary Farmer @ 2000-11-17 00:02 UTC (permalink / raw)
To: Christophe Boyanique <cboyanique@formanet.be>; pgsql-sql
I apologize for the lateness of my reply. I played with your
query. Would the following work for you? It outputs both
idProduct and name, but you can ignore the name. It gets
around the postgres problem with UNIONs in subqueries by
moving the UNION to the query, instead:
SELECT idProduct, name FROM products, orders WHERE idProduct=id and
ts>'2000-10-01 17:04:00'
UNION
SELECT idProduct, name FROM products, preorders WHERE idProduct=id and
ts>'2000-10-01 17:04:00'
ORDER BY name;
I created your tables, inserted some data, and this query
returns the data I would expect. My database is 6.5.2.
Gary Farmer
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Christophe Boyanique
> Sent: Friday, November 10, 2000 10:26 AM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] SELECT FROM t1 WHERE id IN (SELECT id FROM t2 UNION
> SELECT id FROM t3)
>
>
> Hello,
>
> I've got a problem with UNION and SELECT IN
>
> I have a first table:
>
> CREATE TABLE products
> (
> idProduct INT4,
> name VARCHAR(32)
> );
>
> and two others tables:
>
> CREATE TABLE orders
> (
> id INT4,
> ts TIMESTAMP
> );
>
> CREATE TABLE preorders
> (
> id INT4,
> ts TIMESTAMP
> );
>
> (I reduced the tables to be as simple as possible)
>
> I want to retrieve id of products from the tables orders and preorders;
> and order it by the name of the product. So I tried:
>
> SELECT idProduct FROM products
> WHERE idProduct IN
> (SELECT id FROM orders WHERE ts>'2000-10-01 17:04:00'
> UNION SELECT id FROM preorders WHERE ts>'2000-10-01 17:04:00')
> ORDER by name;
>
> and I've got a parse error near UNION or SELECT depending of the
> presence
> of () between the SELECTs.
>
> I definitively need some help to solve this problem :-|
>
> I asked to a friend to test it with Oracle and that seems to work so I
> really don't know what to do...
>
> Christophe.
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2000-11-17 00:02 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-11-10 16:25 SELECT FROM t1 WHERE id IN (SELECT id FROM t2 UNION SELECT id FROM t3) Christophe Boyanique <cboyanique@formanet.be>
2000-11-10 16:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-11-10 16:45 ` Christophe Boyanique <cboyanique@formanet.be>
2000-11-10 18:10 ` Tomas Berndtsson <tomas@nocrew.org>
2000-11-17 00:02 ` Gary Farmer <farmer@arlut.utexas.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox