title: Making sense of Inner-Join with Sub-query in SQL Server 2008
body: <p>I have 2 tables called '<strong>table123</strong>' and '<strong>table246</strong>'. </p>
  
  <p>'<strong>table123</strong>' columns: '<em>ID</em>', '<em>Dept_ID</em>', '<em>First_Name</em>', '<em>Surname</em>', '<em>Salary</em>', '<em>Address</em>'.</p>
  
  <p>'<strong>table246</strong>' columns: '<em>Dept_ID</em>', '<em>Dept_Name</em>'.</p>
  
  <p>I want to find the Average Salary for each '<em>Dept_Name</em>'. So I tried using the query below, which is an Equi-Join with a Sub-query:</p>
  
  <pre><code>SELECT Dept_Name, alt.Average_Salary AS Avg_Salary
  FROM table123 a, table246 b,
  (SELECT Dept_ID, AVG(Salary)Avg_Salary
  FROM table123
  GROUP BY Dept_ID)alt
  WHERE a.Dept_ID = alt.Dept_ID
  AND a.Salary = alt.Average_Salary
  AND a.Dept_ID = b.Dept_ID;
  </code></pre>
  
  <p>However, when I run the above query, it gives the desired 2 column names '<em>Dept_Name</em>' and '<em>Avg_Salary</em>', but with no data in it (just a blank table).</p>
  
  <p>What am I doing wrong in the code, which is causing this blank result table? </p>
  
  <p>Also, is there an alternative method of getting the same result, using an Inner- Join? The Equi-Join is quite confusing.  </p>