Thread: 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
SELECTid 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.

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

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.

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

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