CODEWITHSUNDEEP
sqlsql-serverselect
BoundForGlory
BoundForGlory

Reputation: 4417

Querying the same table twice to get 2 records..in one query

I'm using SQL Server 2016, but this is not a 2016 question. Here's what I would like to do. I will have the user's email address and will need to get data for that user. So this is simple:

select [firstname],[lastname],[managerid] from employees where workemail='[email protected]'

Very straight forward, but notice i'm pulling the manager's id. And i now need the exact same data for the manager, sans the manager's id:

select [firstname],[lastname] from employees where mamangerid=manager_id_from_employee_query

You can see all data is in the same table. I could stop here because I have my data, but I love to learn and look for better solutions. How can i achieve what I'm trying to do without having to write 2 separate SQL statements or what would be the better solution?

Upvotes: 1

Views: 2293

Answers (3)

Mureinik
Mureinik

Reputation: 311163

You can use a self join where the first instance of the table indicated the employee's details and the second the manager's details:

SELECT    e.[firstname], e.[lastname], m.[firstname], m.[lastname]
FROM      employees e
LEFT JOIN employees m ON e.[managerid] = m.[id]
WHERE     e.workemail = '[email protected]'

Upvotes: 2

Webeng
Webeng

Reputation: 7113

This should give you the right result:

SELECT a.firstname, a.lastname FROM employees a RIGHT JOIN 
  (SELECT managerid FROM employees WHERE workemail = '[email protected]') b 
WHERE a.managerid = b.managerid

Upvotes: 1

Xavier J
Xavier J

Reputation: 4624

You can do a left-join. The left side expression will have the employee, and the right side will link to a row in the same table and get the manager's info (assuming it exists). Don't do an inner join because if the "manager" row is missing, then you'll get nothing for the employee.

Select  [e].[firstname] [emp_fname]
  , [e].[lastname] [emp_lname]
  , Manager.[firstname] [mgr_fname]
  , Manager.[lastname] [mgr_lname]
From    [employees] [e]
    Left Join [employees] Manager
        On [e].[employee_id] = Manager.[managerid]

Upvotes: 5

Related Questions