Joining Tables - Help needed

Question

Retrieve the employee number, surname, and forename of all employees who have the letter ‘m’ anywhere in their surname. Also display the department name and location. HINT: The letter ‘m’ may be either upper or lower case.

I am trying to get the 5 rows that I need but I keep getting 20 rows back and I have used the following code but I don't know what else I need to do. I keep getting irrelevant information and cant find the links between the two tables.

select emp_no, emp_surname, emp_forename, depart_name, depart_location 
from employee, department
where emp_surname like 'M%' or emp_surname like '%m%';

Answer 1

You have to use the inner join, and then match with the column value use as lower then use like with m. That's working in SQL server.

select emp_no, emp_surname, emp_forename, depart_name, depart_location 
from employee INNER JOIN department on employee.department_id = department.id
where lower(emp_surname) like 'm%';

Answer 2

You need connection between emplyee and department, for instance :

select e.emp_no, e.emp_surname, e.emp_forename, d.depart_name, d.depart_location 
from employee e, department d
where e.emp_surname like 'M%' or e.emp_surname like '%m%' and .id = d.emplyee_id

Answer 3

You are creating a Cartesian Product of the two tables, since there is no condition set on which rows logically belong together. As a result, every possible combination of an employee and a department is created, and then your selection is done on that.

To limit that, you need to specify which column in employee maps to which column in department.

Something like this:

select emp_no, emp_surname, emp_forename, depart_name, depart_location 
from employee
inner join department on employee.department_id = department.id
where emp_surname like '%M%' or emp_surname like '%m%';