title: Joining tables based on value
body: <p>I'm having some hard time doing the join function on those two tables. I have simplified the example dataset as there are additional where-clauses involved for the first table however that doesn't seem to be a problem.</p>
  
  <p>I would write the query for joining the two tables below:</p>
  
  <pre><code>select a.prod_code, a.prod_name, b.ref_value from Product_code a
  left join Product_reference b on a.prod_code = b.pref_code
  where a.prod_code &lt;&gt; 'CURTAIN' and b.ref_value = 'MAN'
  </code></pre>
  
  <p>The problem I'm facing is that I want to join tables kind of conditionally. I.e. if the ref_type value is 'MAN' in Product_reference table, I do want to join it, otherwise not.</p>
  
  <p>For an example this query would not include "Chair" in the result as it does not have an ref_type 'MAN' available in the "Product_name". What I'd need though is still show it in the query result, just without joined value from the Product_reference table (given that value with ref_type 'MAN' does not exist for it), not leave it out altogether.</p>
  
  <p>Meanwhile Product_name table record 'CURTAIN' should be left off (regardless if Product_reference ref_type 'MAN' exists or not)</p>
  
  <p>Any recommendations?</p>
  
  <pre><code>Product_code    
  prod_code   prod_name
  
  A           Table
  B           Chair
  C           Window
  D           Door
  E           Curtain
  
  
  Product_reference       
  pref_code   ref_type    ref_value
  
  A           MAN         x
  A           AUTO        y
  B           AUTO        z
  C           AUTO        z1
  C           MAN         x1
  D           AUTO        zxc
  E           AUTO        abc
  E           MAN         cba
  </code></pre>