Select one field/value from two different tables

#1

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)

Quote:

| -- (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)

Quote:

|
(select newschool from New where pkey = MMAN.PKEY),

(many fields),
FROM
Middletable MMAN,
(list of other tables),
WHERE
(stuff)
;

Thanks!

#2

JBond007 <nafis121 (AT) yahoo (DOT) com> wrote:

Quote:

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 (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."

#3

JBond007 <nafis121 (AT) yahoo (DOT) com> wrote:

Quote:

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 (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."

#4

JBond007 <nafis121 (AT) yahoo (DOT) com> wrote:

Quote:

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 (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."

#5

JBond007 <nafis121 (AT) yahoo (DOT) com> wrote:

Quote:

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 (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."

#6

On Aug 4, 7:12*pm, t... (AT) panix (DOT) com (Dan Blum) wrote:

Quote:

JBond007 <nafis... (AT) yahoo (DOT) 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 * * * * * * * * * * * * * * * * * * * * * * * * t... (AT) panix (DOT) com *
"I wouldn't have believed it myself if I hadn't just made it up."- Hide quoted text -

- Show quoted text -

If only one value is needed from either old or new then a sclar
subquery could be used inside a coalesce to select the first non-null
value:

UT1 > l
1 select coalesce((select fld1 from marktest where fld2 = 1),
2 (select fld1 from marktest where fld2 = 8))
3* from dual
UT1 > /

COALESCE((
----------
one

In the example above no fld2 = 8 exists. If I reverse the equality
values the same result is produced.

HTH -- Mark D Powell --

#7

On Aug 4, 7:12*pm, t... (AT) panix (DOT) com (Dan Blum) wrote:

Quote:

JBond007 <nafis... (AT) yahoo (DOT) 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 * * * * * * * * * * * * * * * * * * * * * * * * t... (AT) panix (DOT) com *
"I wouldn't have believed it myself if I hadn't just made it up."- Hide quoted text -

- Show quoted text -

If only one value is needed from either old or new then a sclar
subquery could be used inside a coalesce to select the first non-null
value:

UT1 > l
1 select coalesce((select fld1 from marktest where fld2 = 1),
2 (select fld1 from marktest where fld2 = 8))
3* from dual
UT1 > /

COALESCE((
----------
one

In the example above no fld2 = 8 exists. If I reverse the equality
values the same result is produced.

HTH -- Mark D Powell --

#8

On Aug 4, 7:12*pm, t... (AT) panix (DOT) com (Dan Blum) wrote:

Quote:

JBond007 <nafis... (AT) yahoo (DOT) 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 * * * * * * * * * * * * * * * * * * * * * * * * t... (AT) panix (DOT) com *
"I wouldn't have believed it myself if I hadn't just made it up."- Hide quoted text -

- Show quoted text -

If only one value is needed from either old or new then a sclar
subquery could be used inside a coalesce to select the first non-null
value:

UT1 > l
1 select coalesce((select fld1 from marktest where fld2 = 1),
2 (select fld1 from marktest where fld2 = 8))
3* from dual
UT1 > /

COALESCE((
----------
one

In the example above no fld2 = 8 exists. If I reverse the equality
values the same result is produced.

HTH -- Mark D Powell --

#9

On Aug 4, 7:12*pm, t... (AT) panix (DOT) com (Dan Blum) wrote:

Quote:

JBond007 <nafis... (AT) yahoo (DOT) 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 * * * * * * * * * * * * * * * * * * * * * * * * t... (AT) panix (DOT) com *
"I wouldn't have believed it myself if I hadn't just made it up."- Hide quoted text -

- Show quoted text -

If only one value is needed from either old or new then a sclar
subquery could be used inside a coalesce to select the first non-null
value:

UT1 > l
1 select coalesce((select fld1 from marktest where fld2 = 1),
2 (select fld1 from marktest where fld2 = 8))
3* from dual
UT1 > /

COALESCE((
----------
one

In the example above no fld2 = 8 exists. If I reverse the equality
values the same result is produced.

HTH -- Mark D Powell --

#10

On Aug 4, 7:12*pm, t... (AT) panix (DOT) com (Dan Blum) wrote:

Quote:

I'm amazed that that actually parses.

.....I got the same response from some other programmers in my
company,
.....including a DBA who, unfortunately, doesn't know code.
.....Unfortunately, what worked in SQLPlus, from my original msg,
doesn't work in Pro*C,
.....which complains about the word "oldschool" in the larger query's
concatentation.
.....Neither SQLPlus nor Pro*C liked the union in the sub-select or
the word "coalesce",
.....in someone else's response.
.....I also tried if, case, and decode, to no avail (they worked, but
didn't give me what I wanted.
.....I can't believe this is as complicated as it seems!

Quote:

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)