pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: union in subselect?
Date: Tue, 31 Jul 2001 15:42:12 +0100
Message-ID: <01073115421207.11590@gary.ringways.co.uk> (raw)

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     



view thread (2+ messages)  latest in thread

Message-ID: <01073115421207.11590@gary.ringways.co.uk>
Permalink:  ../01073115421207.11590@gary.ringways.co.uk/
Also on:    postgresql.org/message-id/01073115421207.11590@gary.ringways.co.uk

 · 

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 using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: gary.stainburn@ringways.co.uk
  Subject: Re: union in subselect?
  In-Reply-To: <01073115421207.11590@gary.ringways.co.uk>

* 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