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);

Check this https://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join — Alex Mac, Jun 29 '17 at 07:39
This is the best explanation of joins I've yet seen: [Visual Representation of SQL Joins](https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins). — Schwern, Jun 29 '17 at 07:45

score 1 · Answer 1 · answered Jun 29 '17 at 07:43

1

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, ...)

answered Jun 29 '17 at 07:43

Thorsten Kettner

89,309
7
49
73

ok, i get what you are saying. but here is my actual tables. for employee it is jobs. its pk is job_id. for departments, they are Adults, Children, Pets, and Home. for the first 3 they have basically same data columns (id, max_num, start_age, end_Age, special, services, job_id) services are enum of different values depending on table. home has its own services but also a situation that is an enum as well. it does not have any of the others except it also has its own pk. they all references jobs(job_id). how would i turn this int a better database. – Bernardo Jun 29 '17 at 08:59
I suggest you make this a separate request. – Thorsten Kettner Jun 29 '17 at 09:09

score 1 · Accepted Answer · answered Jun 29 '17 at 08:26

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.

would exists work if i am looking for employees that are either in production, accounting, or sales and not only for those are in all three. Just Wondering. — Bernardo, Jun 29 '17 at 09:02
Well, first of all the `EXISTS` clauses can also be written as `IN`, if you find these more readable. (I do.) And sure you can use these to ask for other combinations. Take one of the first two queries and replace `and` with `or`. Or take the latter two queries and replace `>= 2` by `>= 1`. And when looking for employees that work in one department only, use the latter queries with `= 1`. — Thorsten Kettner, Jun 29 '17 at 09:07

can someone give a simple explanation or example of joins?

2 Answers2