CODEWITHSUNDEEP
sqlsql-server
Tee Tom
Tee Tom

Reputation: 17

Creating new columns from data in other columns based on primary key

I'm new to SQL so bear with me. I've managed to have the output list of every employee, their job title, and the employee ID of the employee of they report to (aka their manager). I can't for the life of me figure out how to properly add two additional columns (ManagerName and ManagerJobTitle) with the necessary correct information in it.

Here is my query so far.

Select (FirstName + ' ' + LastName) as EmployeeName, Title as JobTitle, ReportsTo
From Employees E
Order By ReportsTo

Upvotes: 0

Views: 144

Answers (2)

Gabriel Rainha
Gabriel Rainha

Reputation: 1743

You didn't show any schema for your tables, but from your query I can see you are missing the join with the manager table, from where those two columns where probably supposed to come.

Edit: OK, now I get it. The employee table relates to itself. You didn't specified the PK for the table, so I'll assume the Employee has an Id. If the key is another column, just change it in the query below:

Select (E FirstName + ' ' + E.LastName) as EmployeeName, 
      E.Title as JobTitle,           
      (R.FirstName + ' ' + R.LastName) as 'ManagerName', 
      R.Title as 'ManagerTitle'
From Employees E
  LEFT JOIN 
     Employees R ON E.ReportsTo = R.Id
Order By ReportsTo

Edit 2: Changed from INNER JOIN to LEFT JOIN, as a employee can have no manager.

Upvotes: 1

Indra Prakash Tiwari
Indra Prakash Tiwari

Reputation: 1057

Select (E.FirstName + ' ' + E.LastName) as EmployeeName, E.Title as JobTitle, mgr.ReportsTo, (mgr.FirstName + ' ' + mgr.LastName) as ManagerName, mgr.Title as MamagerJobTitle
From Employees E
Left join Employees mgr on E.EmployeeID = mgr.ReportsTo
Order By ReportsTo

Note: As per my understanding, ReportsTo column has employeeID who have report to any manger, if not please replace that column with mgr.ReportsTo

Upvotes: 0

Related Questions