Select one field/value from two different tables
Date: Mon, 4 Aug 2008 15:18:08 -0700 (PDT)
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),
(list of other tables),
Thanks! Received on Mon Aug 04 2008 - 17:18:08 CDT