Thisaru Guruge Thisaru Guruge - 5 months ago 20
SQL Question

Select data from multiple tables and grouping in mysql

This is a part of an assignment I am working on.
I have a database named

COMPANY
, where there are 6 tables


  • EMPLOYEE

  • DEPARTMENTS

  • DEPT_EMP

  • TITLES

  • SALARIES

  • DEPT_MANAGER



Now I have to list the number of Engineers in each department.

I came up with the following query:

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;


But this gives me the error


Unknown column 'dept_emp.emp_no' in 'where clause'


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

Answer

You are missing a join to dept_emp:

select departments.dept_name as Department_name,
       count(titles.title) as No_Of_Engineers
from departments
     INNER JOIN dept_emp
      ON(dept_emp.dept_no = departments.dept_no)
     INNER JOIN titles
      ON(titles.emp_no = dept_emp.emp_no)
WHERE titles.title like "% engineer %"
group by departments.dept_no;

I've also corrected your joins, please try to avoid the use of implicit join syntax(comma separated) and use the proper syntax of joins.