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)
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