Re: Select one field/value from two different tables

On Aug 4, 7:12 pm, t..._at_panix.com (Dan Blum) wrote:
> 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 ;
>
> 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.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))

COALESCE((

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

HTH -- Mark D Powell -- Received on Tue Aug 05 2008 - 08:29:46 CDT