# 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:

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