Received: from relay.mail.insnet.cw.net (relay.mail.insnet.cw.net [213.38.238.97]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6VEgvf63519 for ; Tue, 31 Jul 2001 10:42:58 -0400 (EDT) (envelope-from gary.stainburn@ringways.co.uk) Received: from stan.ringways.co.uk ([213.38.87.130]) by relay.mail.insnet.cw.net (8.11.2/8.11.2) with ESMTP id f6VEgr926090 for ; Tue, 31 Jul 2001 15:42:53 +0100 (BST) Received: from gary.ringways.co.uk ([10.1.1.2]) by stan.ringways.co.uk with smtp (Exim 3.13 #1) id 15RaW6-0000tR-00 for pgsql-sql@postgresql.org; Tue, 31 Jul 2001 15:29:02 +0100 Content-Type: text/plain; charset="iso-8859-1" From: Gary Stainburn Organization: Ringways Garages Ltd To: pgsql-sql@postgresql.org Subject: union in subselect? Date: Tue, 31 Jul 2001 15:42:12 +0100 X-Mailer: KMail [version 1.2] MIME-Version: 1.0 Message-Id: <01073115421207.11590@gary.ringways.co.uk> Content-Transfer-Encoding: 8bit X-Archive-Number: 200107/388 X-Sequence-Number: 4202 Hi all, Can you use (or work around towards) a union in a subquery? I have :- Members mid int4; mnec bool; -- many NEC members Positions (one position = one holder - excludes NEC) posn char(4); pholder int4; -- refers to mid Actions caction char(4) -- e.g. UPDT = update team cposn char(4) -- refers to posn clevle int4 -- increasing permission level select 'NEC' as posn from members where mid = 81 and mnec = true; posn ------ NEC (1 row) select posn from positions where pholder = 81; posn ------ MSEC ITA REG (3 rows) select posn from positions where pholder = 81 union select 'NEC' as posn from members where mnec = true and mid = 81; posn ------ ITA MSEC NEC REG (4 rows) So far so good. select * from actions where cposn in (select posn from positions where pholder = 81); caction | cposn | clevel ---------+-------+-------- ENQT | REG | 2 ENQM | REG | 2 AMET | REG | 2 AMET | ITA | 3 Still works - looking good select * from actions where cposn in (select posn from positions where pholder = 81 union select 'NEC' as posn from members where mnec = true and mid = 81); gives me: ERROR: parser: parse error at or near "union" -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000