b.le b.le - 1 year ago 103
SQL Question

SQL: List the employees who report to those employees who report to (____)

from this table..
Employees Table

Question: List the employees who report to those employees who report to Diane Murphy. Use the CONCAT function to combine the employee's first name and last name into a single field for reporting.

My non-working query..

SELECT employeeNumber, CONCAT(firstName, ' ', lastName), reportsTo
FROM Employees
WHERE reportsto= 1002
AND WHERE (SELECT CONCAT(firstName, ' ', lastName), reportsTo
FROM employees
WHERE reportsTo= 1056 AND 1076);

I am really confused by this question to be honest.

1002= Diane Murphy

1056= Mary Patterson

1076= Jeff Firrelli

Based on my understanding, im trying to query this based on a hierarchal system.
I know that both Mary and Jeff report to Diane Murphy and i can look at the table to see who reports to Mary and Jeff, but im lost exactly i go about querying this.

Any help would be appreciated!

Thank you.

Answer Source

This should do what you want:

select e1.employeeNumber, CONCAT(e1.firstName, ' ', e1.lastName), e1.reportsTo
from Employees as e1 
inner join Employees as e2 on e1.reportsTo = e2.employeeNumber
where e2.reportsTo = 1002
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download