Re: Table Join

From: Palooka <nobody_at_nowhere.com>
Date: Tue, 14 Apr 2009 19:51:45 +0100
Message-ID: <5X4Fl.51017$2S5.10850_at_newsfe22.ams2>

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 Received on Tue Apr 14 2009 - 13:51:45 CDT