Recursive SQL to create managerial hierarchy
-
Thursday, December 06, 2012 11:11 PM
Hello,
I was recently asked to write a SQL insert statement to add rows in the Employee Manger table.
Input Employee -
emp id name email start date mgr name mgr email 10 A1 a1@test.com 10/10/2010 M1 m1@test.com 5 M1 m1@test.com 10/10/2010 M0 m0@test.com 15 A2 a2@test.com 10/10/2010 M1 a2@test.com 1 M0 m0@test.com 10/10/2010 M0 m0@test.com Output Employee Manager-
emp id name email start date mgr emp id 10 A1 a1@test.com 10/10/2010 5 5 M1 m1@test.com 10/10/2010 1 15 A2 a2@test.com 10/10/2010 15 1 M0 m0@test.com 10/10/2010 5 So recursively based on the email I will need to find the mgr emp id for each record in the Employee table.
Note - The email and emp id are unique
Please help.
Thanks,
SP
All Replies
-
Thursday, December 06, 2012 11:24 PM
This is one of the examples in Books Online for use of the APPLY clause:
http://msdn.microsoft.com/en-us/library/ms175156(v=sql.105).aspx
"Premature optimization is the root of all evil." - Knuth
If I provoked thought, please click the green arrow
If I provoked Aha! please click Propose as Answer
We are here to learn, to share knowledge, and to earn points; all in about equal measure.
-
Friday, December 07, 2012 1:40 AM
>> I was recently asked to write a SQL insert statement to add rows in the Employee Manger table. <<
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules (you are not even close). Temporal data should use ISO-8601 formats (you did not). Code should be in Standard SQL as much as possible and not local dialect.
This is minimal polite behavior on SQL forums. What you did post is the totally non-relational UN-normalized adjacency list model for a tree (not a hierarchy). You do not know that rows are not records, that relationships are not entities.
We separate the personnel from the organizational hierarchy:
CREATE TABLE Personnel
(emp_id INTEGER NOT NULL PRIMARY KEY,
emp_name VARCHAR(25) NOT NULL,
emp_email_address VARCHAR(255) NOT NULL UNIQUE,
hire_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL);
CREATE TABLE OrgChart
(emp_name CHAR(10) NOT NULL PRIMARY KEY,
lft INTEGER NOT NULL UNIQUE CHECK (lft > 0),
rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1),
CONSTRAINT order_okay CHECK (lft < rgt));
The (lft, rgt) pairs are like tags in a mark-up language, or parens in algebra, BEGIN-END blocks in Algol-family programming languages, etc. -- they bracket a sub-set. This is a set-oriented approach to trees in a set-oriented language.
I am not going to re-post things about the nested sets model you should Google for yourself. It is one of the basic idioms of SQL programming.I have a book on TREES & HIERARCHIES IN SQL which you can get at Amazon.com right now. It has a lot of other programming idioms for nested sets, like levels, structural comparisons, re-arrangement procedures, etc.
--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
-
Friday, December 07, 2012 5:43 AM
CELKO,
Well my understanding about forums is to help each other and share knowledge. If every knowledgeable person (which I think you are) just asks to refer to a book or google then people will stop posting.
Pieter Geerkens,
The link was very helpful. Thank you.
.png)
