title: inner join does not work
body: <p>I have somehow complicated query as follow:</p>
  
  <pre><code> SELECT w,e2.EntityID
   FROM (SELECT EntityID,SUM(frequency) w
  FROM omid.entity_epoch_data where EpochID in 
  ( select id from epoch where startDateTime&gt;='2013-11-01 00:00:00' and 
  startDateTime &lt;= '2013-11-30 00:00:00') 
        GROUP BY EntityID) e1 
      RIGHT JOIN
      entity e2 ON e1.EntityID = e2.EntityID order by w desc
  </code></pre>
  
  <p>And it works properly but as soon as I add another innerjoin:</p>
  
  <pre><code>SELECT w,e2.EntityID
  FROM (SELECT EntityID,SUM(frequency) w
  FROM omid.entity_epoch_data inner join omid.entity_dataitem_relation as e3            on(e1.EntityID = e3.EntityID) 
  where e3.dataitemtype=3  and EpochID in 
   ( select id from epoch where startDateTime&gt;='2013-11-01 00:00:00' and 
   startDateTime &lt;= '2013-11-30 00:00:00') 
        GROUP BY EntityID) e1 
      RIGHT JOIN
      entity e2 ON e1.EntityID = e2.EntityID order by w desc
  </code></pre>
  
  <p>I get the following error:</p>
  
  <p><strong>column 'EntityID' in the field set is ambiguous</strong></p>
  
  <p>Does anyone have an idea where my mistake is?</p>
  
  <p><strong>Update :</strong></p>
  
  <p>I have the right version as follow (it gives me exactly what I want)</p>
  
  <pre><code>SELECT ID,e2.EntityID,e2.Name,AVG(intensity),AVG(quality),SUM(frequency) 
  FROM omid.entity_epoch_data as e1
  right JOIN entity AS e2 ON (e1.EntityID = e2.EntityID)
  where EpochID in 
  ( select id from epoch where startDateTime&gt;='2013-11-01 00:00:00' and 
  startDateTime &lt;= '2013-11-30 00:00:00') and e1.EntityID in 
  (SELECT entityid FROM omid.entity_dataitem_relation where dataitemtype=3)
   group by e2.EntityID order by sum(frequency) desc;
  </code></pre>
  
  <p>But it takes time and I need to change the </p>
  
  <pre><code>(SELECT entityid FROM omid.entity_dataitem_relation where dataitemtype=3)
   group by e2.EntityID order by sum(frequency) desc; 
  </code></pre>
  
  <p>to innerjoin can anyone help me how to do that?</p>
  
  <p><strong>Image:</strong></p>
  
  <p><img src="https://i.stack.imgur.com/InLbL.png" alt="enter image description here"></p>
  
  <p><img src="https://i.stack.imgur.com/PfkHg.png" alt="enter image description here"></p>