CODEWITHSUNDEEP
sql
user3051442
user3051442

Reputation: 149

SQL ReportsTo Query

So I been struggling with this one for a while. The question is as follows:

Find all the Employees to whom no other employee reports to. Print the Id, and the first and last name of such employees.

The Relationship Sets can be found here:

https://chinookdatabase.codeplex.com/wikipage?title=Chinook_Schema&referringTitle=Documentation

And the Data in the Table is as follows (Note that the rest of the data that is cut off is not needed)

Table

Now, the easiest solution that I immediately saw was that the Sale Support had no one report to them:

Select DISTINCT E.EmployeeId, E.FirstName, E.LastName    
From Employee E
Where E.Title = 'Sales Support Agent'

But that seems like a cheap work around, is there a way to get the answer using EmployeeID and ReportsTo only?

Upvotes: 1

Views: 1024

Answers (3)

Doolius
Doolius

Reputation: 874

This should give you what you are wanting: 

Select 
    E.EmployeeId
    ,E.FirstName
    ,E.LastName    
From Employee E
Where E.EmployeeID NOT IN (SELECT DISTINCT ReportsTo
                           FROM Employee
                           WHERE ReportsTo is not null)

Selects every employee that has no one who reports to them.

Upvotes: 1

A. El-Helw
A. El-Helw

Reputation: 201

This should work:

Select E.EmployeeId, E.FirstName, E.LastName    
From Employee E
Where E.EmployeeID NOT IN (SELECT ReportsTo FROM Employee WHERE ReportsTo IS NOT NULL)

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

SQL Fiddle Demo

 SELECT E.*
 FROM Employee E
 LEFT JOIN Employee R  -- R for report to
   ON E.EmployeeID = R.ReportTo
 WHERE R.EmployeeID IS NULL

OUTPUT

| EmployeeID | LastName | ReportTo |
|------------|----------|----------|
|          3 |  Peacock |        2 |
|          4 |     Park |        2 |
|          5 |  Johnson |        2 |
|          7 |     King |        6 |
|          8 | Callahan |        6 |

Upvotes: 4

Related Questions