Re: Select one field/value from two different tables
Date: Tue, 5 Aug 2008 10:23:11 -0700 (PDT)
On Aug 4, 5:18 pm, JBond007 <nafis..._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"
> (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 ;
> In reality, I have to extract a LOT more data from a LOT more tables
> in the one query,
> so the above query isn't anywhere as simple as I've made it out to be
> (i.e., the "12345" value is replaced by another table's primary key
> which is common to "PKEY" in the above tables),
> but I want to know if the above will work,
> or will I get bogus or NULL values when I try it with the real and
> much larger query.
> Here's a sample of the larger query in Pro*C:
> select (many fields),
> (select oldschool from Old where pkey = MMAN.PKEY)
> (select newschool from New where pkey = MMAN.PKEY),
> (many fields),
> Middletable MMAN,
> (list of other tables),
Why do you want to do it in the SELECT Clause?
Wouldn't this work?
select (many fields), school,
(list of other tables),
-- inline view
(select oldschool school, pkey from Old UNION
select newschool school, pkey from New ) schoolview WHERE
MMAN.PKEY = schoolview.pkey
Ed Received on Tue Aug 05 2008 - 12:23:11 CDT