-1

I'm supposed to write a query for a database that is a little tricky for me. It has an EMPLOYEE table that has these attributes(columns): Emp_Id,Name,Age, Dept_ID, Salary

It also has a DEPARTMENT table that has these attributes(columns): Dep_Id, Dep_Name, Location, Mgr_Id.

I am being asked to list the Employees Names, their Locations and their Manager's Name. The Manager is(of course) an employee Meaning I'm goign to have to select from Name twice. So how can I make a query where I can display both the Name of both the Employee and the manager. I can do one or the other but I have no clue how to do both in the same query.

0

Assuming the Mgr_Id has a foreign key relation with Emp_id in the EMPLOYEE table, you can do the following:

SELECT
e.Name as EmployeeName,
d.Location AS EmployeeLocation,
de.Name AS ManagerName
FROM EMPLOYEE AS e
LEFT OUTER JOIN DEPARTMENT AS d
ON d.Dep_Id = e.Dept_ID
LEFT OUTER JOIN EMPLOYEE AS de
ON de.Mrg_Id = e.Emp_Id

The key point to take away is the use of AS to use table aliases so you can reference a table more than once, for different records. Technically, the AS itself is optional, but I left it in for clarity.

  • Thanks. That really helped. I had to tweak it a bit but it set me on the right track. – Carl Jansen Nov 17 '14 at 1:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.