Re: Table Join

From: ddf <oratune_at_msn.com>
Date: Tue, 14 Apr 2009 11:03:46 -0700 (PDT)
Message-ID: <a8c03f88-4784-4028-a8e1-ff0f2d58e839_at_l16g2000vba.googlegroups.com>

On Apr 14, 12:24 pm, John Schaeffer <ame..._at_iwc.net> wrote:
> On Apr 14, 12:10 pm, ddf <orat..._at_msn.com> wrote:
>
>
>
>
>
> > On Apr 14, 11:50 am, John Schaeffer <ame..._at_iwc.net> wrote:
>
> > > Hi, I'm hoping that there is a slick way to do this.
>
> > > I have 2 tables (below).  They both have the same structure except for
> > > the first column.  Is there a way to compare the tables and get the
> > > differences from TABLE A that are not in TABLE B, without having to
> > > use WHERE...AND...AND...AND for every column??
>
> > > TABLE A
> > > --------------
> > >  TICKER
> > >  CATEGORY_NAME
> > >  TOTAL_RETURN_1YR
> > >  TOTAL_RETURN_6MO
> > >  TOTAL_RETURN_3MO
> > >  TOTAL_RETURN
> > >  PERFORMANCE_RANK
> > >  ORDINAL_1YR
> > >  ORDINAL_6MO
> > >  ORDINAL_3MO
> > >  ORDINAL_RANK
>
> > > TABLE B
> > > --------------
> > >  TIME
> > >  TICKER
> > >  CATEGORY_NAME
> > >  TOTAL_RETURN_1YR
> > >  TOTAL_RETURN_6MO
> > >  TOTAL_RETURN_3MO
> > >  TOTAL_RETURN
> > >  PERFORMANCE_RANK
> > >  ORDINAL_1YR
> > >  ORDINAL_6MO
> > >  ORDINAL_3MO
> > >  ORDINAL_RANK
>
> > You COULD try this:
>
> > select
> > TICKER,
> > CATEGORY_NAME,
> > TOTAL_RETURN_1YR,
> > TOTAL_RETURN_6MO,
> > TOTAL_RETURN_3MO,
> > TOTAL_RETURN,
> > PERFORMANCE_RANK,
> > ORDINAL_1YR,
> > ORDINAL_6MO,
> > ORDINAL_3MO,
> > ORDINAL_RANK
> > from tablea
> > minus
> > select
> > TICKER,
> > CATEGORY_NAME,
> > TOTAL_RETURN_1YR,
> > TOTAL_RETURN_6MO,
> > TOTAL_RETURN_3MO,
> > TOTAL_RETURN,
> > PERFORMANCE_RANK,
> > ORDINAL_1YR,
> > ORDINAL_6MO,
> > ORDINAL_3MO,
> > ORDINAL_RANK
> > from tableb;
>
> > Searching the newsgroup would have found similar solutions.
>
> > David Fitzjarrell
>
> I know, was just hoping to avoid all the column listings.....thought
> someone came up with a cool solution......- Hide quoted text -
>
> - Show quoted text -

There are no 'magic bullets' in Oracle, and you do not have the same structure in both tables. You have no choice but to list the columns in your select. At least the MINUS syntax doesn't require you to JOIN anything, which eliminates the JOIN and WHERE .. AND ... AND ... AND syntax you stated you wished to avoid. The solution provided, then, is 'slick' in comparison.

David Fitzjarrell Received on Tue Apr 14 2009 - 13:03:46 CDT