pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gary Farmer <farmer@arlut.utexas.edu>
To: Christophe Boyanique <cboyanique@formanet.be>
To: pgsql-sql@postgresql.org
Subject: RE: SELECT FROM t1 WHERE id IN (SELECT id FROM t2 UNION SELECT id FROM t3)
Date: Thu, 16 Nov 2000 18:02:32 -0600
Message-ID: <NDBBKNPOIKAFAGAOPAPNIEIBCDAA.farmer@arlut.utexas.edu> (raw)
In-Reply-To: <3A0C2193.DB793E1B@formanet.be>

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




view thread (5+ messages)

Message-ID: <NDBBKNPOIKAFAGAOPAPNIEIBCDAA.farmer@arlut.utexas.edu>
Permalink:  ../NDBBKNPOIKAFAGAOPAPNIEIBCDAA.farmer@arlut.utexas.edu/
Also on:    postgresql.org/message-id/NDBBKNPOIKAFAGAOPAPNIEIBCDAA.farmer@arlut.utexas.edu

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: farmer@arlut.utexas.edu, cboyanique@formanet.be
  Subject: Re: SELECT FROM t1 WHERE id IN (SELECT id FROM t2 UNION SELECT id FROM t3)
  In-Reply-To: <NDBBKNPOIKAFAGAOPAPNIEIBCDAA.farmer@arlut.utexas.edu>
  References: <3A0C2193.DB793E1B@formanet.be> <NDBBKNPOIKAFAGAOPAPNIEIBCDAA.farmer@arlut.utexas.edu>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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