Re: Table Join

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. :-)