title: Hive: Invalid Column Reference
body: <p>In Hive, I've got four tables:</p>
  
  <ul>
  <li>temp_basic_info (ID, MSISDN, GENDER, AGE, DAY, MONTH, YEAR, RELATIONSHIPSTATUS)</li>
  <li>temp_education (ID, EDUCATION)</li>
  <li>likes_and_music (ID, NAME, PAGE)</li>
  <li>temp_output (ID, MSISDN, GENDER, AGE, DAY, MONTH, YEAR, RELATIONSHIPSTATUS, EDUCATION, LIKES_AND_PREFERENCES)</li>
  </ul>
  
  <p>temp_output is empty.</p>
  
  <p>Now, I want to transfer the appropriate fields from the other three tables into temp_output. likes_and_music has multiple instances of the same ID's, paired with varying NAMEs and PAGEs, so I'd have to put them in an array.</p>
  
  <p>My projected output is something like the following:</p>
  
  <pre><code>0001 msisdn1 male 21 1 2 92 0 College [Jeep, soccer, PC games, etc...]
  </code></pre>
  
  <p>And here's my query so far:</p>
  
  <pre><code>Select a.ID, a.MSISDN, a.GENDER, a.AGE, a.DAY, a.MONTH, a.YEAR, a.RELATIONSHIPSTATUS, b.EDUCATION, COLLECT_SET(c.NAME) FROM temp_basic_info a JOIN temp_education b ON (a.ID = b.ID) JOIN likes_and_music c ON (c.ID = b.ID) GROUP BY a.ID, a.MSISDN, a.GENDER, a.AGE, a.DAY, a.MONTH, a.YEAR, a.RELATIONSHIPSTATUS, b.EDUCATION, c.name limit 10;
  </code></pre>
  
  <p>But the latter returns the following error:</p>
  
  <p><strong>FAILED: SemanticException [Error 10002]: Line 1:311 Invalid column reference 'EDUCATION'</strong></p>
  
  <p>What am I missing?</p>