Re: Table Join

From: ddf <oratune_at_msn.com>
Date: Tue, 14 Apr 2009 13:14:05 -0700 (PDT)
Message-ID: <6e6e5bc8-531a-4ac8-888a-823b30d08f0e_at_o34g2000vbi.googlegroups.com>

On Apr 14, 2:52 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Apr 14, 11:51 am, Palooka <nob..._at_nowhere.com> wrote:
>
>
>
>
>
> > ddf wrote:
> > > 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
>
> > Besides which, it would have been a damn sight quicker for OP just to
> > enter the column listings than to waste time on usenet.
>
> > Palooka
>
> It might have been slick to use user_tab_columns to generate the sql
> dynamically.  Of course, sick might be the better word.  Peformance
> would likely suck, if it's not a one-time thing.  So it could be a so
> slick sick sucky sql select, since Schaeffer solicited some site. :-)
>
> jg
> --
> _at_home.com is bogus.http://blog.wired.com/27bstroke6/2006/06/the_newbies_gui.html?entry_i...- Hide quoted text -
>
> - Show quoted text -

Don't hold back, Joel, tell us how you really feel...

David Fitzjarrell Received on Tue Apr 14 2009 - 15:14:05 CDT