title: Oracle - Left outer join with where clause
body: <p>I have a join on two tables defined as a left outer join so that all records are returned from the left hand table even if they don't have a record in the right hand table. However I also need to include a where clause  but.... I still want a row from the left-hand table to be returned for each record in the left-hand table even if the condition in the where clause isn't met. Is there a way of doing this?</p>
  
  <p>I am writing the query with the join condition like </p>
  
  <pre><code>SELECT A.*, B.* 
  FROM A 
  LEFT OUTER JOIN B ON A.VIN = B.VIN AND 
  TRUNC(a.REP_OPEN_DATE) BETWEEN TRUNC(b.CHECK_IN_DATE)+1 AND TRUNC(b.CHECK_IN_DATE)-1
  </code></pre>
  
  <p>above condition not returning any rows. where as below condition returns...</p>
  
  <pre><code>SELECT A.*, B.* 
  FROM A 
  LEFT OUTER JOIN B ON A.VIN = B.VIN
  </code></pre>
  
  <p>I need the data from the left table even if the below condition doesn't met...</p>
  
  <pre><code>TRUNC(a.REP_OPEN_DATE) BETWEEN TRUNC(b.CHECK_IN_DATE)+1 AND TRUNC(b.CHECK_IN_DATE)-1
  </code></pre>
  
  <p>can some one please help.</p>
  
  <p>A TABLE:-</p>
  
  <pre><code>VIN    | RO_OPEN_DATE
  1234   | 04-NOV-13
  6789   | 09-NOV-13
  </code></pre>
  
  <p>B TABLE</p>
  
  <pre><code>VIN    | CHECK_IN_DATE
  1234   | 09-NOV-13
  1234   | 05-NOV-13
  6789   | 20-OCT-14
  6789   | 29-OCT-14
  </code></pre>
  
  <p>OUTPUT SHOULD BE</p>
  
  <pre><code>   VIN     | RO_OPEN_DATE  | CHECK_IN_DATE
     1234    | 04-NOV-13     | 05-NOV-13
     6789    | 09-NOV-13     | NULL
  </code></pre>
  
  <p><code>Condition :-For EACH RO_OPEN_DATE for a VIN, We need to check if we have +/- 1 day of CHECK_IN_DATE from RO_OPEN_DATE.</code></p>