title: Oracle SQL query and perhaps subqueries across 4 tables
body: <p>I have 4 tables and need to get values from 2 of them.</p>
  
  <p><strong>policy</strong> policy_id, template_id</p>
  
  <p><strong>quote</strong> quote_id, policy_id</p>
  
  <p><strong>quote_clause</strong> quote_clause_id, quote_id</p>
  
  <p><strong>quote_clause_variable</strong> quote_clause_variable_id, quote_clause_id, variable_name,     value</p>
  
  <p>-</p>
  
  <ul>
  <li><p>Each policy policy_id has 2-3 records of quote quote_id.</p></li>
  <li><p>Each quote quote_id has 100-200 records of quote_clause<br>
  quote_clause_id.</p></li>
  <li><p>Each quote_clause_id thas600-700 records of quote_clause_variable.</p></li>
  </ul>
  
  <p>I'm trying to get all the policy_id's that have a specific template_id of 28 and 
  from there, get the quote_clause_variable value where the variable name is 'R01011C1'.</p>
  
  <p>I'm a little lost at this point, neither of the queries below are anywhere near correct.</p>
  
  <pre><code>SELECT policy.policy_id, quote_clause_variable.value 
  FROM mciaweb.policy 
  LEFT JOIN mciaweb.quote ON policy_id = policy_id 
  LEFT JOIN mciaweb.quote_clause ON quote_id = quote_id 
  LEFT JOIN mciaweb.quote_clause_variable ON quote_clause_id = quote_clause_id 
  WHERE policy.template_id = '28' 
  AND variable_name = 'R01011C1';
  
  
  select 
  (select value from mciaweb.quote_clause_variable 
  where variable_name in ('R01011C1') 
  and quote_clause_id in 
  (select quote_clause_id from MCIAWEB.quote_clause where quote_id in 
  (select quote_id from MCIAWEB.quote where policy_id in 
  (select policy_id from mciaweb.policy where template_id in ('28') 
  )))) situation, 
  policy_id  
  from mciaweb.policy where template_id = '28'
  ;
  </code></pre>
  
  <p>Many thanks in advance for any help with this.</p>