Select data from multiple tables and grouping in mysql

Question

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

There are no dept_emp in the from clause. I.e. the WHERE clause looks like there are 3 tables involved, but you're only selecting from 2 tables. — jarlh
– jarlh, Commented Jun 28, 2016 at 9:56
...from departments, titles, dept_emp... shouldn't there be dept_emp table in the from clause? — 1000111
– 1000111, Commented Jun 28, 2016 at 9:58
@jarlh Is it compulsory ? Because I don't want emp_no in my result. — ThisaruG
– ThisaruG, Commented Jun 28, 2016 at 9:58
I can't answer that question because I don't know the tables and the data. You don't have to include columns from all tables in the select list. — jarlh
– jarlh, Commented Jun 28, 2016 at 9:59

sagi · Accepted Answer · 2016-06-28 09:58:58Z

1

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.

answered Jun 28, 2016 at 9:58

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ThisaruG Over a year ago

Thanks for the answer. Can you explain why am I needed to avoid implicit joins ?

sagi Over a year ago

They are unreadable and may lead to mistakes in the future! Perhaps when joining two/three tables you can still understand, but when another programmer will try to work a code he didn't built, containing like 6 or more tables, it will be very hard to understand! And don't let me talk about LEFT JOINs with the plus sign . It's a bad habit and you should get used to the proper syntax.

sagi Over a year ago

That's becuase you probably gave the wrong name. Look at departments table, what's the name of department_number column ?

ThisaruG Over a year ago

The column name was dept_no. But I've fixed the error bygrouping them by department_name. Thank you very much for the help !

Collectives™ on Stack Overflow

Select data from multiple tables and grouping in mysql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related