title: oracle left outer joins not showing right null values
body: <p>i'm having an issue with creating a query in oracle which doesnt seem to want to join on missing values </p>
  
  <p>the table i have is this:</p>
  
  <pre><code>table myTable(refnum, contid, type)
  
  values are:
  1, 10, 90000
  2, 20, 90000
  3, 30, 90000
  4, 20, 10000
  5, 30, 10000
  6, 10, 20000
  7, 20, 20000
  8, 30, 20000
  </code></pre>
  
  <p>a break down of the fields i'm after is this:</p>
  
  <pre><code>select a.refnum from myTable a where type = 90000
  select b.refnum from myTable b where type = 10000 and contid in (select contid from myTable where type = 90000)
  select c.refnum from myTable c where type = 20000 and contid in (select contid from myTable where type = 90000)
  </code></pre>
  
  <p>the outcome of the query i'm after is this:</p>
  
  <pre><code>a.refnum, b.refnum, c.refnum
  </code></pre>
  
  <p>i thought this would work:</p>
  
  <pre><code>select a.refnum, b.refnum, c.refnum
  from myTable a 
  left outer join myTable b on (a.contid = b.contid) 
  left outer join myTable c on (a.contid = c.contid) 
  where a.id_tp_cd = 90000
  and b.id_tp_cd = 10000
  and c.id_tp_cd = 20000
  </code></pre>
  
  <p>so the values should be:</p>
  
  <pre><code>1, null, 6
  2, 4, 7
  3, 5, 8
  </code></pre>
  
  <p>but its only returning:</p>
  
  <pre><code>2, 4, 7
  3, 5, 8
  </code></pre>
  
  <p>i thought left joins would show all values in the left and create a null for the right.</p>
  
  <p>help :(</p>