CODEWITHSUNDEEP
mysqlsql
ThisaruG
ThisaruG

Reputation: 3412

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

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

Upvotes: 0

Views: 115

Answers (1)

sagi
sagi

Reputation: 40491

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.

Upvotes: 1

Related Questions