Re: Select one field/value from two different tables

From: Dan Blum <>
Date: Mon, 4 Aug 2008 23:12:11 +0000 (UTC)
Message-ID: <g782cb$m2f$>

JBond007 <> wrote:
> I have two tables: "Old" and "New".
> Both table have the same primary key name and type: "PKEY", NUMBER.

> "Old" table has a column called "oldschool",
> while "New" table has a column called "newschool".
> Both columns are the same type (NUMBER) and cannot be null.

> I'm told that the two tables are mutually exclusive,
> such that the same PKEY value cannot exist in both tables,
> and that if it isn't in the "Old" table, then it must be in "New"
> table
> (i.e., it must exist in one or the other table, but not both).

> To get one or the other value, I tried the following,
> which seems to work in SQLPlus:

> SELECT (select oldschool from Old where pkey = 12345)
> || -- (i.e., concatenate)
> (select newschool from New where pkey = 12345)
I'm amazed that that actually parses.

In any case, what you want to do is replace the concatentation operator with UNION - or, if the tables are really mutually exclusive, UNION ALL (UNION removes duplicates, which if you don't have any duplicates just wastes time). E.g.,

(select oldschool from old where pkey = 12345  union
 select newschool from new where pkey = 12345)

Dan Blum					
"I wouldn't have believed it myself if I hadn't just made it up."
Received on Mon Aug 04 2008 - 18:12:11 CDT

Original text of this message