I rаn іnto аn interesting problem todаy whіle considering how to fіnd out whеre subordinate employees fіt іnto аn organizational ϲhart. Τhe problem wаs thаt I nеed to lіst еvery employee thаt wаs “undеr” a gіven employee, but ϲould onlу really do thіs іn ЅQL - іf I wеre to trу аnd do thіs from within ΡHP, іt would hаve mаde a single exception ϲase whеre wе buіld аn ЅQL quеry bаsed on another quеry - something I’d rather not hаve to do. Lеt’s ѕee іf thіs little grаph ϲan morе adequately describe whаt thе іssue wаs:
(morе…)
Ѕhare Τhis
10 Comments
Very well written article but I still dont see the point of it.
Thank you for sharing this. I had a similar issue looking at categories of various levels (category-subcate-subsubcate .. etc), and your script helped tremendously. One thing I’d like to point out though, from following your code, is that you missed a space in “ALLSELECT” (making it “ALL SELECT”). Me with my entry level sql knowledge thought it was some secret keyword I’ve never came across.
very interesting article. wish i had known this earlier, would have saved me a lot of trouble. but knowing it now with such a clear explanation is very much worth appreciating the author.
Thanks! I never knew that this method existed. It has saved me a lot of time.
Thank you for this! I used your example to replace a cursor method I was using and it cut the query response time to about half.
Very nice article!
- Glen
Do you want to know if your employees are working or are wasting time?…
In our days productivity has become the God of industry, so all employers expect employees to work faster, work smarter and work more….
Would a ‘connect by’ statement work here?
select employee, subordinate, level
from orgChart
start with employee = ‘Frank’
connect by subordinate = prior employee
If I’m not mistaken, this would produce a list of employees who report to Frank. ‘level’ in this case would produce an incremented number based on the ‘level’ below Frank.
I’m a little confused by the implied schema of the orgChart. I think it would be better if each employee record held a reference to the supervisor, but whatever.
I understand how MPTT can be used for easy retrieval from a tree. However, am I missing something, or will every change (insert/delete/move) to a tree mean that the right/left values of every node in the tree may need to be rebuilt? It seems to me that any time the tree changes, you have to do a recursive traversal to rebuild the MPTT indices, and only after that could you take advantage of MPTT’s easy retrieval. Of course in some situations it’s a worthwhile tradeoff - I just want to make sure I’m characterizing this correctly. Thanks, -Juan
[…] Creating Recursive SQL Calls for Tables with Parent-Child Relationships at Webinade (tags: sql) […]
I guess this works, but a much cleaner, clearer and efficient way to achieve this is to use an MPTT (modified pre-order tree traversal) table. It employs the use of a ‘left’ and ‘right’ value for each node in the tree, and with one sql query (no temporary views/tables) you can get the complete (and ordered) list of all children, or if you know the child and want all parents, you can get a breadcrumb trail back up the tree as well with one query. Work smart, not hard