Outer Join

[Aloe68]

Hi everybody,
my problem is the following:

I've two tables, A and B, with an item key necessary to link them. I want to retrieve data from table B that do not match with data in table A.

I already made this in SQL Server (using right join) and it's working well.

Now I've to do the same in Oracle (8i).
The following is my SELECT statement in Oracle but... it doesn't work properly! I mean, there is no error message but I am not able to retrieve non-matching data...

SELECT A.LAYER_NAME,B.LAYER_NAME FROM TABLE_A A,TABLE_B B WHERE A.LAYER_NAME = B.LAYER_NAME (+);

Waiting for someone helping me...
Thanks in advance!

 

[sem]

select LAYER_NAME from TABLE_B
minus
select LAYER_NAME from TABLE_A

Regards, Dima

 

[jimbopalmer]

Dima I think you are doing what he said, not what he meant.

he said "I want to retrieve data from table B that do not match with data in table A." and your code does that.

I think he meant "I want to match data in table A to table B even if Table B has no rows that match", his code does that.

If table A is th one without matches, SELECT A.LAYER_NAME,B.LAYER_NAME FROM TABLE_A A,TABLE_B B WHERE A.LAYER_NAME(+) = B.LAYER_NAME;

I tried to remain child-like, all I acheived was childish.

 

[Aloe68]

What I mean is:
in the Table A there are 10 records, in the Table B there are 11 records. The first 10 recs of Table B are identical of the recs in Table A: I want to select, from the Table B, the record that is not present in Table A.

I hope now it's best explained.

So, maybe formula's Sem is correct... but an error message stops the execution of the query: "invalid column name." (the two tables are identical).

So... what you suggest me?
Thanks!

 

[sem]

Can you provide your query? Maybe they're identical but each subquery (or at least one) has some typo.

Regards, Dima

 

[Aloe68]

My query is the following:

sql = "(SELECT NAME_TAB FROM DB_CONTENUTI) MINUS (SELECT NAME_TAB FROM AXL_LAYER)"

The two tables have the following structure:

ID_TIP number 2
NOME_TAB varchar2 50
ALIAS varchar2 100
SCALA long
ANNO number 2
UTENTE varchar2 20

I want to select, using NOME_TAB item, the record that is present in DB_CONTENUTI and not in AXL_LAYER. (I made this query in a VB6 program).

Thanks!

 

[sem]

So NOME_TAB or NAME_TAB??? Can you copy your query directly from sql*plus as well as output of DESC DB_CONTENUTI and DESC AXL_LAYER? Can you also be a bit more careful?

Regards, Dima

 

[Aloe68]

Ooops!!! I made a mistake!!!! I'm sorry!!
The correct item name is NOME_TAB.

Anyway, now the query works fine but it returns all the records in DB_CONTENUTI table including the one with no maches in AXL_LAYER table (that is the only record I'm looking for!)...

Any more help?
Thanks!

 

[Turkbear]

Hi,
Try

sql = "(SELECT NOME_TAB FROM DB_CONTENUTI where NOME_TAB NOT IN (SELECT NOME_TAB FROM AXL_LAYER))"

 

[tav1035]

Aloe68,
I have the same issue.
So what was the final and complete query you used to query Oracle?
tav

 

[Aloe68]

I've tried also the query of Turkbear but... again all the records were selected!
....
Any other ideas??
Thanks

 

[sem]

I suppose you don't need "any other ideas". Just run MINUS query correctly. The problem may be with fields that look like similar but differ in some invisible symbols like blank spaces or tabs. In this case you should trim them.

Regards, Dima

 

[Aloe68]

Dima/sem you're partially right, there was no invisible symbols to trim but a letter, for each record, was in upper case!

So, speaking to Tav1035, the correct query is TABLE_B MINUS TABLE_A.

Thanks to all!

 

[Aloe68]

I forgot to say: also the Turkbear's query is good! Now it provides the same result of the one with MINUS.
Bye!