Re: Table Join

From: joel garry <joel-garry_at_home.com>
Date: Tue, 14 Apr 2009 12:52:35 -0700 (PDT)
Message-ID: <18ce795d-272c-4fb4-a2d0-0eec457bc186_at_w31g2000prd.googlegroups.com>

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_id=1510938

Received on Tue Apr 14 2009 - 14:52:35 CDT