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