Re: which kind of join?
Before you can write a correct query, you need to describe the requirement accurately. "select all rows from TABLE A that aren't referenced from TABLE B with a VALUE='aaaa'" would be ...
select distinct a.*
from a
join b
where b.name<>'aaaa';
+------+------+
| ID | NAME |
+------+------+
| 1 | abc |
| 2 | def |
| 3 | ghi |
+------+------+
... but the spec for the result you say you want is "select all rows from TABLE A that aren't referenced from TABLE B
with a matching id and with a VALUE='aaaa'". which would be ...
select a.*
from a
left join b on a.id=b.id_table_a and b.name='aaaa'
where b.id is null;
+------+------+
| ID | NAME |
+------+------+
| 2 | def |
| 3 | ghi |
+------+------+
... left join on ... where b... is null is an
exclusion join.
Edited 1 time(s). Last edit at 06/11/2014 10:25AM by Peter Brawley.
Subject
Written By
Posted
Re: which kind of join?
June 11, 2014 10:23AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.