How do I select employee and manager of department SQL

Question

1.00/5 (1 vote)

See more:

I have three tables:
employees (emp_ID, emp_Name, emp_Dpet, emp_Job) [emp_Dept and emp_Job are foreign Keys. and emp_Job here contain Manager and other jobs like eng...]
departments (dept_ID, dept_Name)
and Jobs (job_ID, job_Name)
what is the query to select Manager of department for employee like:
employee [mark], so he is from deptartment [Sales] and his manager is [Jhon].

What I have tried:

I tried the query:
SELECT emp_Name, dept_Name, emp_Job
FROM employees, departments, Jobs
WHERE e.emp_Dept = d.dept_ID
AND job_Name = 'Manager'
But it select all managers

Posted 25-Mar-21 8:47am

User_Michel

Updated 25-Mar-21 23:56pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

W∴ Balboos, *GHB* · Answer 1 · 2021-03-25T09:00:00

Solution 1

You need to learn about using JOIN statements - particularly INNER JOIN.

You can do that here[^].

You will need to determine how an employee is connected to their title and whatever else in whatever other tables you wish to use. It's much like a WHERE but it is instructing how two table compare on some shared value.

Posted 25-Mar-21 9:00am

W∴ Balboos, GHB

Comments

User_Michel 25-Mar-21 15:22pm

Thank you for your solution. i will learn it because i am beginner in SQL, but in this case what is the query, emp and mgr in one table?

Maciej Los 29-Mar-21 5:39am

5ed!

Richard Deeming · Answer 2 · 2021-03-25T23:56:00

Try something like this:

SQL

WITH cteEmployees As
(
    SELECT
        E.emp_Name,
        E.emp_Dept,
        J.job_Name
    FROM
        employees As E
        INNER JOIN emp_Job As J
        ON J.job_ID = E.emp_Job
)
SELECT
    E.emp_Name,
    D.dept_Name,
    E.job_Name,
    M.emp_Name
FROM
    cteEmployees As E
    INNER JOIN departments As D
    ON D.dept_ID = E.emp_Dept
    LEFT JOIN cteEmployees As M
    ON M.emp_Dept = D.dept_ID
    And M.job_Name = 'Manager'
WHERE
    E.job_Name != 'Manager'
;

But that's not really a good design. You could have multiple managers linked to the same department, which would end up duplicating the employees.

Ideally, you would either have a foreign key from the department table to the employee table to identify the manager, or a foreign key from the employee table back to the employee table if you want to be able to specify a different manager for each employee.