SQLTeam.com | Weblogs | Forums

Joining a Table Onto Itself


#1

Would appreciate if you data-mining whizzes would help out a noob.

I'm running a query to find out who a sales rep reports to and this is the query that I have.

select productName, customerName, contactFirstName, contactLastName, orderDate, lastName, firstName, reportsTo
from orderdetails OD

join orders ORD
on OD.orderNumber = ORD.orderNumber
join products PROD
on OD.productCode = PROD.productCode
join customers CUST
on ORD.customerNumber = CUST.customerNumber
join employees EMP
on CUST.salesRepEmployeeNumber = EMP.employeeNumber

where productName = '1995 Honda Civic'

There is a 'reportsTo' column within the 'employees' table. I cannot figure out how to reference it correctly to show the name of the person that 'employeeNumber' reports to. The employee and the supervisor who they report to both have unique 'firstName' and 'lastName' references in the 'employees' table. Each employee in the table has a reference number within the 'employees' table under the column 'reportsTo'. I need to get the query to return the first and last name of the 'reportsTo' reference for an employee within the table.

If I made this confusing at any point I will clarify. It is confusing, which is why what advanced data-miners can do (waaay beyond the scope of this query) is so impressive.


#2

please post the table structure with sample data


#3


#4

Add another JOIN to the employees table with the relationship between the ReportsTo column and the employee number:

JOIN Employees RT ON RT.employeeNumber = EMP.ReportsTo


#5

Thank you!