Click here to Skip to main content
15,298,916 members
Please Sign up or sign in to vote.
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
Updated 25-Mar-21 23:56pm

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.
   
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!
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.
   
Comments
User_Michel 26-Mar-21 9:07am
   
Really Thank you for your solution, it helped me except changing [INNER JOIN emp_job] to [INNER JOIN jobs].
The design in this project like this, because it must be one manager for each department. but your opinion is right in case if manager changed, so i will try to do your first advice. thanks alot again.
Maciej Los 29-Mar-21 5:39am
   
5ed!

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900