MySQL employees database example

Question

I'm playing with the employees mysql database example to try to understand the query for multiples tables. The thing is that I'm trying to get a table with this result.

emp_no, name, last name, from date, to date, dept_name

and with this query:

SELECT 
  employees.emp_no, 
  employees.first_name, 
  employees.last_name, 
  dept_emp.from_date, 
  dept_emp.to_date, 
  departments.dept_name 
FROM (employees, dept_emp, departments) 
WHERE employees.emp_no = dept_emp.emp_no 
  AND departments.dept_no = dept_emp.dept_no

I got the result but if an employer has more depts the table gives me more row for the same employer.

I want to know if this is normal or if is there any other way to query that. I saw some examples with join but i don't know how to use it with three tables.

What about `GROUP BY dept_emp.dept_no` ? – Luiz May 26 '17 at 17:17 — Luiz, May 26 '17 at 17:17
read about using `JOIN` statements – tereško May 26 '17 at 17:17 — tereško, May 26 '17 at 17:17
Using GROUP BY should be enough for you – javier_el_bene May 26 '17 at 17:17 — javier_el_bene, May 26 '17 at 17:17

datadever · Accepted Answer · 2017-05-26T17:43:59.943

2

This is the perfect example of what you'd use a join for. See the example below on how to use the join syntax. The query below will yield the same result as your query but is a more conventional syntax. The advantages is that you can separate your joining logic and your filtering (WHERE clause) logic. If you needed to filter the results of the join, it would be a simple WHERE clause, rather than having both your join logic and filtering logic in the WHERE clause.

SELECT employees.emp_no, 
       employees.first_name, 
       employees.last_name, 
       dept_emp.from_date, 
       dept_emp.to_date, 
       departments.dept_name 
FROM employees e
INNER JOIN dept_emp de
    ON e.emp_no = de.emp_no
INNER JOIN departments d
    ON de.dept_no = d.dept_no

We start off by selecting from the employees table, joining to the dept_emp table. The on clause specifies fields need to match to join the two tables. Same with dept_emp to departments.

In this example I've used INNER JOIN which will only return matching rows. For a full list of joins and how they work you can find tons of resources explaining. Here is a good resource: https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

EDIT: In response to the other part of your question:

I got the result but if an employer has more depts the table gives me more row for the same employer.

I want to know if this is normal or if is there any other way to query that. I saw some examples with join but i don't know how to use it with three tables. This is all new for me.

Yes this is normal, if there are multiple departments per employee, each department will be a row with the same emp_no.

edited May 26 '17 at 17:43

answered May 26 '17 at 17:21

datadever

36
3

Can you clarify how this answer's the question (specifically, it seems that the OP is asking about getting multiple rows when an employee has more than one department) – devlin carnate May 26 '17 at 17:24
Updated my answer. – datadever May 26 '17 at 17:27
I'm still not sure how your response that discusses INNER JOINS applies. The OP's query has the joins defined in the WHERE clause. Your example differs only in a matter of syntax, as far as I can see. – devlin carnate May 26 '17 at 17:29
"I saw some examples with join but i don't know how to use it with three tables. This is all new for me." I interpreted this as them asking about joins. I agree that it's a difference in syntax but if OP is learning, I'd be more inclined to teach them how to do joins rather than using the method they posted. – datadever May 26 '17 at 17:30
_"WHERE employees.emp_no = dept_emp.emp_no AND departments.dept_no = dept_emp.dept_no"_ This is no different (in the result) than your syntax. If the OP needed to filter the results, or control the order of the join sequence, then yes, explicit `INNER JOIN` would be appropriate. But in this case, I'm failing to see the difference between the result of your syntax and the OP's syntax. – devlin carnate May 26 '17 at 17:31
I'm not saying it's different, but OP specifically said they didn't know how joins work, my answer shows how joins work. My argument is that it's better to use the syntax I used to join rather than the way OP did for a number of reasons, including what you mentioned above "If the OP needed to filter the results, or control the order of the join sequence, then yes, explicit INNER JOIN would be appropriate." – datadever May 26 '17 at 17:36
But your answer doesn't say that ;) You recommend INNER JOIN (which is essentially what the OP is already doing). You don't say why that syntax is better, or how it impacts the results of the OP's example query. I think that's my point ;) – devlin carnate May 26 '17 at 17:38
Fair point. I've updated the answer. Thanks for the clarification. :D – datadever May 26 '17 at 17:44
Datadever, you are really answering my question because i need to filter the resuelt with a where like clausure. Thanks for your good answer. – Pablo Nahuel May 26 '17 at 21:24

score 0 · Answer 2 · answered May 26 '17 at 17:23

0

The best way to achieve this is by using INNER_JOIN.

answered May 26 '17 at 17:23

upaang saxena

779
1
6
18

2

The join is in the WHERE clause in the OP's example. It is an INNER JOIN, but it's different syntax. See here: https://stackoverflow.com/questions/2241991/in-mysql-queries-why-use-join-instead-of-where – devlin carnate May 26 '17 at 17:26

MySQL employees database example

2 Answers2