Select data from multiple tables and grouping in mysql
<p>This is a part of an assignment I am working on.
I have a database named <code>COMPANY</code>, where there are 6 tables</p>

<ul>
<li><code>EMPLOYEE</code></li>
<li><code>DEPARTMENTS</code></li>
<li><code>DEPT_EMP</code></li>
<li><code>TITLES</code></li>
<li><code>SALARIES</code></li>
<li><code>DEPT_MANAGER</code></li>
</ul>

<p>Now I have to list the number of Engineers in each department.</p>

<p>I came up with the following query:</p>

<pre><code>select departments.dept_name as Department_name,
       count(titles.title) as No_Of_Engineers
from departments,
     titles
where titles.emp_no = dept_emp.emp_no
  and dept_emp.dept_no = departments.dept_no
  and titles.title like "% engineer %"
group by departments.dept_no;
</code></pre>

<p>But this gives me the error</p>

<blockquote>
  <p>Unknown column 'dept_emp.emp_no' in 'where clause'</p>
</blockquote>

<p>But my <code>dept_emp</code> table has a column named <code>emp_no</code>.
Can anyone see the error in this?
Thanks in advance</p>