3

I have a table called employees that contains the columns.

ID | Name | Salary | Department_id | Boss_id

So all employees are listed in this no matter where they're bosses or not. If someone is not a boss then their value for Boss_id will be NULL.

The aim is, to figure out if any employees earn more than their respective bosses and by respective I mean, from the same department.

I've been working on this trying to figure it out and I'm not sure if I need a loop to loop through all the departments or if there is an easier way.

Praveen Prasannan
  • 7,093
  • 10
  • 50
  • 70
Lola Wormald
  • 35
  • 1
  • 5

2 Answers2

4

No, you don't need a loop.

If you want to compare the salary of an employee to the salary of his "boss", you can use a JOIN operation.

To list only those employees that have a higher salary than their boss:

SELECT e.*
  FROM employee e
  JOIN employee b
    ON b.id = e.boss_id
   AND e.salary > b.salary

If you want to list all employees, and just add a column that indicates whether their salary is higher than their bosses salary, along with bosses salary:

SELECT e.*
     , IF(e.salary>b.salary,'Y','N') AS higher_salary_than_boss
     , b.salary AS boss_salary
  FROM employee e
  LEFT
  JOIN employee b
    ON b.id = e.boss_id
 ORDER
    BY e.id

I don't see how "department" really comes into play.

If you want to compare an employee's salary to of his bosses boss, you could add yet another LEFT JOIN to the employee table.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • awesome, I've been round in circles today but learnt so much.. Now I need to learn this method of joining.. Thanks a bunch – Lola Wormald Sep 10 '13 at 17:41
  • I don't understand what b and e stand for on their own, can you explain please? :) – Lola Wormald Sep 10 '13 at 19:24
  • In the example queries, the "`e`" and "`b`" are aliases assigned to the `employee` table, within the context of the statement. We can make use of that table alias to qualify column references. e.g. "`e.salary`" to reference the `salary` column from the employee table. The choice of the aliases is arbitrary, except I chose them not to conflict with any other identifiers in the statement, and I prefer shorter aliases. I chose "`e`" as the alias for the employee table we're retreiving employee information from; I chose "`b`" as the alias for the table we're pulling the related "boss" from. – spencer7593 Sep 10 '13 at 19:29
  • Absent those aliases, if we were to reference just the bare (unqualified) column name "`salary`", that would be ambiguous, the DBMS wouldn't "know" which table to return the salary from. In those cases. Oracle (fortunately) throws an "ambiguous column" exception. So best practice is to qualify all column references in any query that references more than one row source. And using short aliases assigned to to each table reference is a common practice. – spencer7593 Sep 10 '13 at 19:33
  • Ok thanks I think I'm starting to understand but still a lil confused the "b.id = e.boss_id" statement doesnt seem to work... These are the columns that we want to be matched together I guess? – Lola Wormald Sep 10 '13 at 20:31
  • Ok so we are creating 2 separate tables from the one table then merging the 2? – Lola Wormald Sep 10 '13 at 20:32
  • oh man something just started to make sense, at last a penny has dropped I think I have been doing to much today and can't take much more in... I understand this now but the thing is still saying error Unknown column 'b.id' in 'on clause' – Lola Wormald Sep 10 '13 at 20:36
2
SELECT t1.ID, t1.Name 
FROM table1 t1 
INNER JOIN table2 t2
ON t1.ID = t2.Boss_id
AND t1.Salary > t2. Salary
AND t1.Department_id = t2.Department_id
Praveen Prasannan
  • 7,093
  • 10
  • 50
  • 70