pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
SELECT 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>
  2000-11-10 16:35 ` Re: SELECT FROM t1 WHERE id IN (SELECT id FROM t2 UNION SELECT id FROM t3) Tom Lane <tgl@sss.pgh.pa.us>
  2000-11-17 00:02 ` RE: SELECT FROM t1 WHERE id IN (SELECT id FROM t2 UNION SELECT id FROM t3) Gary Farmer <farmer@arlut.utexas.edu>
  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: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   ` Re: SELECT FROM t1 WHERE id IN (SELECT id FROM t2 UNION SELECT id FROM t3) Christophe Boyanique <cboyanique@formanet.be>
  2000-11-10 18:10   ` Re: SELECT FROM t1 WHERE id IN (SELECT id FROM t2 UNION SELECT id FROM t3) Tomas Berndtsson <tomas@nocrew.org>
  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: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 ` Re: SELECT FROM t1 WHERE id IN (SELECT id FROM t2 UNION SELECT id FROM t3) Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-11-10 16:45   ` Christophe Boyanique <cboyanique@formanet.be>
  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 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 ` Re: SELECT FROM t1 WHERE id IN (SELECT id FROM t2 UNION SELECT id FROM t3) Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-11-10 18:10   ` Tomas Berndtsson <tomas@nocrew.org>
  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-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-17 00:02 ` Gary Farmer <farmer@arlut.utexas.edu>
  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