Re: Select one field/value from two different tables

JBond007 <nafis121_at_yahoo.com> 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)
> FROM DUAL ;
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					         tool_at_panix.com	
"I wouldn't have believed it myself if I hadn't just made it up."