Re: Select one field/value from two different tables

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"
> 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 ;
>
> 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),
> FROM
> Middletable MMAN,
> (list of other tables),
> WHERE
> (stuff)
> ;
>
> Thanks!

Why do you want to do it in the SELECT Clause?

Wouldn't this work?

select (many fields), school,
 (many fields),
 FROM
  Middletable MMAN,
  (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
  AND (stuff)

Ed Received on Tue Aug 05 2008 - 12:23:11 CDT