title: Oracle left join
body: <p>I have two tables A1,A2
  A1 (primary key ID):    </p>
  
  <pre><code>| ID    |   NAME  |
  |-------|---------|  
  | 1     |   Cat1  |      
  | 2     |   Cat2  |   
  | 3     |   Cat3  |   
  | 4     |   Cat4  | 
  | 5     |   Cat5  |   
  </code></pre>
  
  <p>and A2 (primary key ID, foreign key A1_ID=A1.ID)</p>
  
  <pre><code>| ID    |   NAME  | A1_ID  |  TYPE  |
  |-------|---------|--------|--------|    
  | 1     |   Sub1  |   1    |    L   |  
  | 2     |   Sub2  |   2    |    F   |    
  | 3     |   Sub3  |   3    |    V   |     
  | 4     |   Sub4  |   4    |    L   |
  | 5     |   Sub5  |   4    |    V   |
  | 6     |   Sub6  |   5    |        |
  </code></pre>
  
  <p>I am trying to get all the results from both tables where A2.Type is L or F or null</p>
  
  <p>This is what I have up to now:</p>
  
  <pre><code>select a.*, b.*
       from  a1 a
   left join a2 b
          on a.id=b.a1_id
      where (b.type='L'  
          or b.type='F'
          or b.type is null)
  </code></pre>
  
  <p>which returns :</p>
  
  <pre><code>| ID    |   NAME  |  ID    |   NAME | A1_ID  |  TYPE  |
  |-------|---------|--------|--------|--------|--------|  
  |   1   |   Cat1  |   1    |  Sub1  |   1    |    L   |
  |   2   |   Cat2  |   2    |  Sub2  |   2    |    F   |
  |   4   |   Cat4  |   4    |  Sub4  |   4    |    L   |
  |   5   |   Cat5  |   6    |  Sub6  |   5    |        |
  </code></pre>
  
  <p>But I am looking for a query that it will exclude the line with A1.ID = 4 because with the same A1_ID there is a row with TYPE=V</p>
  
  <pre><code>| ID    |   NAME  |  ID    |   NAME | A1_ID  |  TYPE  |
  |-------|---------|--------|--------|--------|--------|  
  |   1   |   Cat1  |   1    |  Sub1  |   1    |    L   |
  |   2   |   Cat2  |   2    |  Sub2  |   2    |    F   |
  |   5   |   Cat5  |   6    |  Sub6  |   5    |        |
  </code></pre>
  
  <p>Any ideas?</p>