can someone give a simple explanation or example of joins?

Question

Here is an theoretical example of a problem. i am making a database schema for a business.

I have a table for person, employee, client, production, sales, accounting, and administration humanResources.

  Person: 
  contact_id  (primary key)
  first_name
  last_name
  phone
  email

  Employee: 
  employee_id (primary key)
  contact_id (foreign key)  References Person(contact_id)
  date_start

  Production: 
  production_id (primary key)
  employee_id (foreign key) References Employee(employee_id)
  //other information

  Sales: 
  sales_id (primary key)
  employee_id (foreign key)  References Employee(employee_id)
  //other information

  Accounting: 
  accounting_id (primary key) 
  employee_id (foreign key) References Employee(employee_id)
  //other information


  Administration:
  administrator_id (primary key)
  employee_id (foreign key) References Employee(employee_id)
  //other information

  HumanResources:
  humanResources_id (primary key)
  employee_id (foreign key) References Employee(employee_id)
  //other information 

  Client
  client_id (primary key)
  contact_id (foreign key) References Person(contact_id)
  employee_id (foreign key) References Employee(employee_id)
  //other information

Client is a bad name for the table. it mainly keeps track on what employee a client was in contact with, and we can assume what the conversation is about depending on the department the employee is working in at that time.

Assuming that an employee may work in more than one department, return the first name and last name of employees that work in at least one of certain two or three of the companies departments.

is it some thing like this:

   SELECT b.first_name, b.last_name FROM Employee a 
   LEFT JOIN Person b USING(contact_id) 
   INNER JOIN Production c USING(employee_id) 
   WHERE a.employee_id IN (SELECT contact_id FROM Client)
   UNION    
   SELECT b.first_name, b.last_name FROM Employee a 
   LEFT JOIN Person b USING(contact_id) 
   INNER JOIN Accounting c USING(employee_id) 
   WHERE a.employee_id IN (SELECT contact_id FROM Client)
   UNION
   SELECT b.first_name, b.last_name FROM Employee a 
   LEFT JOIN Person b USING(contact_id) 
   INNER JOIN Sales c USING(employee_id) 
   WHERE a.employee_id IN (SELECT contact_id FROM Client);

Answer 1

As to your original question: It's not all about joins. For your data model, you want to find persons that work in all three departments Production, Accounting, Sales. This is a condition and hence belongs in the WHERE clause.

select p.first_name, p.last_name
from person p
join employee e using (contact_id)
where e.employee_id in (select employee_id from production)
  and e.employee_id in (select employee_id from accounting)
  and e.employee_id in (select employee_id from sales);

Or even:

select first_name, last_name
from person
where contact_id in
(
  select contact_id
  from employee
  where employee_id in (select employee_id from production)
    and employee_id in (select employee_id from accounting)
    and employee_id in (select employee_id from sales)
);

And if you want employees that work in at least two of the departments, count:

select first_name, last_name
from person
where contact_id in
(
  select contact_id
  from employee e
  where 
    (select count(*) from production p where p.employee_id = e.employee_id) +
    (select count(*) from accounting a where a.employee_id = e.employee_id) +
    (select count(*) from sales s where s.employee_id = e.employee_id) >= 2
);

Or use EXISTS:

select first_name, last_name
from person
where contact_id in
(
  select contact_id
  from employee e
  where exists (select * from production p where p.employee_id = e.employee_id) +
        exists (select * from accounting a where a.employee_id = e.employee_id) +
        exists (select * from sales s where s.employee_id = e.employee_id) >= 2
);

The latter works, because in MySQL true = 1, false = 0.

Answer 2

This is a strange database design. A department should be data, not an Entity. As much as you don't want to create a new table for a new employee, but only add a row to the employees table, you'd want to add a record to a departments table when adding a new department and not create a new table.

So think about installing a better database. E.g.:

• employee (employee_id, fist_name, last_name, phone, ...)
• department (department_id, department_name, ...)
• employee_department (employee_id, department_id, salary, ...)