title: LEFT JOIN Columns With WHERE Clause in Oracle Entity Attribute Value(EAV) Database Tables
body: <p>I have a USERS table like this</p>
  
  <p><strong>USERS</strong></p>
  
  <pre><code>user_id | username
  1         tom
  2         sam
  </code></pre>
  
  <p>I also have a USER_META table like this</p>
  
  <p><strong>USER_META</strong></p>
  
  <pre><code>user_meta_id | user_id | meta_key | meta_value
  1              1         active     1
  2              1         car        dodge
  3              2         active     0
  4              2         car        honda
  </code></pre>
  
  <p>My problem is that I need to select the meta_keys <em>active</em> and and <em>car</em> but only for users who have a value of 1 for <em>active</em> so my result set should be something like this</p>
  
  <pre><code>user_id | user_name | user_meta_id | meta_key | meta_value
  1         tom         1              active     1
  1         tom         2              car        dodge
  </code></pre>
  
  <p>I have tried a few things but I cannot get this type of result set. Here is what I thought might work</p>
  
  <pre><code>SELECT * FROM USERS
  LEFT JOIN USER_META 
     ON USERS."user_id" = USER_META 
     AND (USER_META."meta_key" = 'active' OR USER_META."meta_key" = 'car') 
  WHERE (USER_META."meta_key" = 'active' 
  AND USER_META."meta_value" = 1)
  </code></pre>
  
  <p>the problem with this is that I get a result set that is missing the car meta_key/meta_value</p>
  
  <pre><code>user_id | user_name | user_meta_id | meta_key | meta_value
  1         tom         1              active     1
  </code></pre>
  
  <p>How can I modify the query to get all of the meta information? thanks.</p>