Skip to content
Advertisement

Select within the same table

I’m studying SQL statements and i got stucked in this scenario.

The problem is to select data that has associantion column with another in the same table, the result that i want is to select ” Name of employe”, “Reports to” , “The name of your boss” (selecting the name associating with the employeId)

[1]: https://pasteboard.co/IcqUYbB.png "execution of queries"

I’ve tried selecting in tha same querie the columns associating them, but didn’t work.

/*1*/
SELECT employeeNumber,firstName, (select firstname  from employees where reportsTo = employeeNumber) as boss from employees;

/*2*/
SELECT e.firstName ,'Reports to' as 'Report', (SELECT e2.firstName from employees e2 where e.employeeNumber = e2.reportsTo) AS Boss  
from employees e;

Both failed

I’m getting

Advertisement

Answer

USE LEFT SELF JOIN to also include the Main Boss

select 
lower.employeeNumber
,lower.firstName
,higher.firstName as boss
from employees lower
left outer join employees higher
   on higher.employeenumber = lower.reportsto
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement