Is this possible in native SQL?

[mshields]

Is the following query possible in native, straight SQL (no procedures, no outside programming)?

An employees table contains ID, name, salary, and supervisorID. The ID and supervisorID fields are in the same domain (i.e. my Boss's ID = my supervisorID). Find all employees who earn more than his/her management, direct or indirect.

 

[bdreed35]

This will certainly return employees who make more than there direct supports. When you say indirect, are you looking for your boss's boss? Is so, how may levels could there be in the reporting structure?

SELECT A.ID, A.name
FROM employees A, employees B
WHERE A.ID = B.supervisorID
AND A.salary > B.salary

~Brian

 

[mshields]

Thank you for the response! Yes, by indirect, I mean my boss's boss, but it could also be anyone "up the corporate ladder." That is the tricky part of this query (if it is even doable). I would like to compare an employee's salary to everyone above him/her in the corporate hierarchy, but there is not a pre-determined number of levels.

 

[r937]

no way to do it in standard sql, unless it's in some recent version, like sql-1999 or sql-2003...

in any case, even if it is defined in some sql standard, what matters is whether your particular database supports it

oracle has a proprietary extension for this sort of thing, but otherwise, for an indeterminate number of levels up, you will have to write recursive code in some programming language

if you don't have oracle but can arbitrarily set a limit of, say, thirteen levels up, then you can do it in straight sql

(i pulled the number thirteen outta my hat -- thirteen levels oughta be enough to cover 98% of all the bloated organization structures you are ever likely to encounter, and if you have more levels than that, then you are almost guaranteed to be able to dump this entire problem off on some other department which does have oracle)

rudy

http://r937.com/

SQL Consulting