InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 06:27:14 GMT20http://www.sqlservercentral.com/Forums/Topic1423378-392-1.aspxLooking, thank you!Thu, 14 Mar 2013 11:51:19 GMTpolkadothttp://www.sqlservercentral.com/Forums/Topic1423378-392-1.aspxOk. I'm totally confused as to what Ken was trying to demonstrate. Let's take his first query from your last post.[code="sql"]select chartdepth=1, employee=o2.employee, supervisor=o1.employeeinto #org_chartfrom staff o1 inner join staff o2 on (o1.employee=o2.supervisor)where o1.supervisor is not null[/code]The result of that query is the same as the following and requires no join at all because all that it is ultimately doing is returning the contents of the original table with an extra column added.[code="sql"] SELECT ChartDepth = 1, Employee, Supervisor FROM dbo.Staff;[/code]The second "paragraph" of code is clever but it produces a very nasty Cartesian Product (full Cross Join). I don't know if that would resolve itself out to a simple hash join when the table get's larger but, if it doesn't, this code would take a millenia to run on what some folks consider to be a "small" million node hierarchy that frequents the world of MLMs and some parts lists.The third "paragraph" of code is, again, clever, but I'm not sure that you'd ever want to display something like that for a larger hierarchy and so I don't understand the overall reason for demonstrating this method.Getting back to (literally) the root of what I was talking about (Groucho needs to have a NULL for supervisor), the original code isn't easily modified to allow for such a thing. Again, although it's clever code, I'm not sure why anyone would want to display the output of this code. I guess the question at this point is, what would you actually like to do with this hierarchy example? Something like this? Although I don't particularly like the column names or the table name, I kept them for comparison.[code="sql"] CREATE TABLE dbo.Staff ( Employee INT NOT NULL, Employee_Name VARCHAR(10) NOT NULL, Supervisor INT NULL CONSTRAINT PK_Staff PRIMARY KEY CLUSTERED (Employee ASC), CONSTRAINT FK_Staff_ManagerMustBeAnEmployee FOREIGN KEY (Supervisor) REFERENCES dbo.Staff (Employee), CONSTRAINT CK_Staff_ManagerCannotBeSelf CHECK (Supervisor<>Employee) ); INSERT INTO dbo.Staff (Employee,Employee_Name,Supervisor) SELECT 1, 'Groucho' , NULL UNION ALL --1 UNION ALL SELECT 2, 'Chico' , 1 UNION ALL SELECT 3, 'Harpo' , 2 UNION ALL SELECT 4, 'Zeppo' , 2 UNION ALL SELECT 5, 'Moe' , 1 UNION ALL SELECT 6, 'Larry' , 5 UNION ALL SELECT 7, 'Curly' , 5 UNION ALL SELECT 8, 'Shemp' , 5 UNION ALL SELECT 9, 'Joe' , 8 UNION ALL SELECT 10, 'Curly Joe' , 9;WITH cteDirectReports AS ( --=== This gets the top-level supervisors(s). -- This would be the "primer" for a loop. SELECT Employee, Supervisor, Employee_Name, EmployeeLevel = 1, HierarchicalPath = CAST('\'+CAST(Employee AS VARCHAR(10)) AS VARCHAR(4000)) FROM dbo.Staff WHERE Supervisor IS NULL UNION ALL --==== This gets a level at a time using recursion of the CTE. -- This would be the "body" of a loop. SELECT e.Employee, e.Supervisor, e.Employee_Name, EmployeeLevel = d.EmployeeLevel + 1, HierarchicalPath = CAST(d.HierarchicalPath + '\'+CAST(e.Employee AS VARCHAR(10)) AS VARCHAR(4000)) FROM dbo.Staff e INNER JOIN cteDirectReports d ON e.Supervisor = d.Employee ) --=== This simply formats for display. I wouldn't do this in real life, either. SELECT Employee, Supervisor, Employee_Name = SPACE((EmployeeLevel-1)*4) + Employee_Name, EmployeeLevel, HierarchicalPath FROM cteDirectReports ORDER BY HierarchicalPath;[/code]That produces the following indented name result...[code="plain"]Employee Supervisor Employee_Name EmployeeLevel HierarchicalPath1 NULL Groucho 1 \12 1 Chico 2 \1\23 2 Harpo 3 \1\2\34 2 Zeppo 3 \1\2\45 1 Moe 2 \1\56 5 Larry 3 \1\5\67 5 Curly 3 \1\5\78 5 Shemp 3 \1\5\89 8 Joe 4 \1\5\8\910 9 Curly Joe 5 \1\5\8\9\10[/code]The "Hierarchical Path" contains the chain of command from the top level all the way down to the individual employee. If done in a Binary(4) format, you can easily use it to do some really remarkable things. It's kind of like the "new" HierarchyID datatype but better, IMHO.For a much more detailed explanation of how that works, please see the following article.[url]http://www.sqlservercentral.com/articles/T-SQL/72503/[/url]Wed, 06 Mar 2013 20:30:50 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1423378-392-1.aspxAh...my apologies. I lost track of this thread. On my way to work. I'll try to post an example tonight.Wed, 06 Mar 2013 07:11:31 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1423378-392-1.aspxLike this? (All the same queries, but modified with Null. This leaves Groucho out altogether).[code="plain"]create table staff(employee int primary key, employee_name varchar(10),supervisor int null references staff (employee))insert staff values (1, 'groucho', Null),(2, 'chico', 1),(3, 'harpo', 2),(4, 'zeppo', 2),(5, 'moe', 1),(6, 'larry', 5),(7, 'curly', 5),(8, 'shemp', 5),(9, 'joe', 8),(10, 'curly joe', 9);select chartdepth=1, employee=o2.employee, supervisor=o1.employeeinto #org_chartfrom staff o1 inner join staff o2 on (o1.employee=o2.supervisor)where o1.supervisor is not nullinsert into #org_chartselect distinct o1.chartdepth+1, o2.employee, o1.supervisorfrom #org_chart o1 join #org_chart o2 on (o1.employee=o2.supervisor)where o1.chartdepth=(select max(chartdepth) from #org_chart)and o1.supervisor is not nullselect s.employee_name, supervises='supervises', e.employee_namefrom #org_chart o join staff s on (o.supervisor=s.employee)inner join staff e on (o.employee=e.employee)where o.supervisor<>o.employee[/code]Mon, 04 Mar 2013 22:07:27 GMTpolkadothttp://www.sqlservercentral.com/Forums/Topic1423378-392-1.aspxMaybe, but I'd have done it a different way. The original data already has more than 1 level.The original data also has a flaw in it that makes it impossible to enforce the rule that no one should report to themselves to prevent cycles in code. Groucho's superviser ID should have been NULL. A NULL would not interfere with the reference that a superviser ID must also be an employee ID.Sun, 03 Mar 2013 17:25:15 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1423378-392-1.aspxlevity.....I tried.:laugh: He's given [i]me[/i] a hard time beforeThank you Jeff!!![quote]I cannot, for the life of me, figure out why Ken wanted to create six copies of the same hierarchy with all people at all levels reporting to the same supervisor.[/quote]I thought it was to help the reader understand multiple layers of chartdepth. After a few iterations you exhaust all the chartdepths possible and can run a query to extract all boss->subordinate relationships. Groucho is the head boss, but there are a few minor bosses as well. It's not perfectly linear. I thought that was a good approach, before launching into the While loop....to demonstrate what the while loop does. That part was actually good for me.Sun, 03 Mar 2013 16:40:18 GMTpolkadothttp://www.sqlservercentral.com/Forums/Topic1423378-392-1.aspx[quote][b]polkadot (3/3/2013)[/b][hr]Well, if that's all it is, then great. I tend to overblame myself. I was sure it was me.Well, Joe Celko forewarded the book. I would have thought he would have caught it. [/quote]Nah, not to worry... Overblaming yourself is actually a good thing. Those that do the opposite tend to fall in the arrogant category and that's when code really begins to suffer.I ran the code you provided and it's really odd to me. I cannot, for the life of me, figure out why Ken wanted to create six copies of the same hierarchy with all people at all levels reporting to the same supervisor.I agree with Lynn, as well. Just because someone forwards a book, doesn't mean they even looked at the content never mind actually doing any technical reviews. Even some authors don't check their own code and not all technical reviewers are good technical reviewers.Sun, 03 Mar 2013 16:38:04 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1423378-392-1.aspx[quote][b]polkadot (3/3/2013)[/b][hr]Well, if that's all it is, then great. I tend to overblame myself. I was sure it was me.Well, Joe Celko forewarded the book. I would have thought he would have caught it. [/quote]Just because he wrote a forward doesn't mean he did a tech review of the book.Sun, 03 Mar 2013 16:23:08 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1423378-392-1.aspxWell, if that's all it is, then great. I tend to overblame myself. I was sure it was me.Well, Joe Celko forewarded the book. I would have thought he would have caught it. Sun, 03 Mar 2013 16:04:22 GMTpolkadothttp://www.sqlservercentral.com/Forums/Topic1423378-392-1.aspx[quote][b]polkadot (3/3/2013)[/b][hr]Can anyone explain the reason K.H. may have shown one supposedly working example without the "SELECT seq=IDENTITY(int)" and one with?[/quote]Yes. It's easy. He made a mistake. It happens.Sun, 03 Mar 2013 15:52:12 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1423378-392-1.aspx[quote][b]polkadot (2/25/2013)[/b][hr]SQL_Enthusiast, I double and triple checked the book and the WHILE query in my original post is a facsimile.The where clause condition is slightly different, it excludes o1.supervisor<>o1.employee. The JOIN is on o1.employee=o2.supervisor. I'm not sure if that makes a difference....[/quote]I didn't notice the post above before. Is the code you most recently posted an exact duplicate of Ken's code or not?Sun, 03 Mar 2013 15:49:28 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1423378-392-1.aspxBut this works (a subsequent lesson)....and the only difference is the SELECT seq=IDENTITY(int). "This approach uses the IDENTITY() function with SELECT...INTO to add an identity column to the work table. It then uses this column to sort the result set when returning it." (K.H. p 245)[code="plain"]select seq=identity(int), chartdepth=1, employee=o2.employee,supervisor=o1.employeeinto #org_chartfrom staff o1 join staff o2 on (o1.employee=o2.supervisor);while (@@rowcount > 0) begininsert #org_chart (chartdepth, employee, supervisor)select distinct o1.chartdepth+1, o2.employee, o1.supervisorfrom #org_chart o1 join #org_chart o2 on (o1.employee=o2.supervisor)where o1.chartdepth=(select max(chartdepth) from #org_chart)and o1.supervisor<>o1.employeeendselect s.employee_name, supervises='supervises', e.employee_namefrom #org_chart o join staff s on (o.supervisor=s.employee)inner join staff e on (o.employee=e.employee)where o.supervisor<>o.employeeorder by seq[/code]Can anyone explain the reason K.H. may have shown one supposedly working example without the "SELECT seq=IDENTITY(int)" and one with?Sun, 03 Mar 2013 15:41:41 GMTpolkadothttp://www.sqlservercentral.com/Forums/Topic1423378-392-1.aspxReiterating problem. This works:[code="plain"]--DDL for creating and populating staff tablecreate table staff(employee int primary key, employee_name varchar(10),supervisor int null references staff (employee))insert staff values (1, 'groucho', 1),(2, 'chico', 1),(3, 'harpo', 2),(4, 'zeppo', 2),(5, 'moe', 1),(6, 'larry', 5),(7, 'curly', 5),(8, 'shemp', 5),(9, 'joe', 8),(10, 'curly joe', 9);--query #1--establishes first 10 rows of table, with first level of chart depthselect chartdepth=1, employee=o2.employee, supervisor=o1.employeeinto #org_chartfrom staff o1 inner join staff o2 on (o1.employee=o1.supervisor)--query#2--following the execution of query #1 run the below query about 4-5 times (f5 x 5), #org_chart will be populated with about 5 levels of chart depthinsert into #org_chartselect distinct o1.chartdepth+1, o2.employee, o1.supervisorfrom #org_chart o1 join #org_chart o2 on (o1.employee=o2.supervisor)where o1.chartdepth=(select max(chartdepth) from #org_chart)---verify population of #org_chart with this querySelect * from #org_chart;---query #3 returns boss -> subordinate relationshipsselect s.employee_name, supervises='supervises', e.employee_namefrom #org_chart o join staff s on (o.supervisor=s.employee)inner join staff e on (o.employee=e.employee)where o.supervisor<>o.employee[/code]HOWEVER, if you want to spare yourself hitting f5 five times, you are supposed to be able to run this query instead:[code="plain"]while (@@rowcount > 0) begininsert #org_chart (chartdepth, employee, supervisor)select distinct o1.chartdepth+1, o2.employee, o1.supervisorfrom #org_chart o1 join #org_chart o2 on (o1.employee=o2.supervisor)where o1.chartdepth=(select max(chartdepth) from #org_chart)and o1.supervisor<>o1.employeeend[/code]But this while query doesn't work. What corrections need to be made to while query to do the job?Sun, 03 Mar 2013 14:15:48 GMTpolkadothttp://www.sqlservercentral.com/Forums/Topic1423378-392-1.aspxOK, I'll check these links out but in the end I know I will come around to wanting to understand KH's chapter. His code the 'long way' works, but his code with the While statement doesn't and I want to know why....But, first, I want to understand hierarchies. So I will check out links.........which i just did and they are for people who already have at least a basic understanding of hierarchies such as what an adjacency list is and how it's supposed to be maintained....I came to Ken Henderson, in order to get the fundamentals in hierarchies. I am working my way through the book as a way to improve my SQL querying. It would be very helpful to have someone look at this specific example....he's a bit of a legend/icon in the SQL community. Anywone have a copy of the Guru's Guide copyright 2000? Anyone willing to take a look at that While loop and see if they know why it's not working?Sun, 03 Mar 2013 13:54:16 GMTpolkadothttp://www.sqlservercentral.com/Forums/Topic1423378-392-1.aspx[quote][b]polkadot (2/23/2013)[/b][hr]I’m working on the hierarchy lesson in Ken Henderson’s The Guru’s Guide to Transact-SQL.[/quote]I'd like to recommend a different approach to hierarchies. Please see the following 2 articles. And I guarantee the code in the articles works.[url]http://www.sqlservercentral.com/articles/Hierarchy/94040/[/url][url]http://www.sqlservercentral.com/articles/T-SQL/94570/[/url]Mon, 25 Feb 2013 23:38:36 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1423378-392-1.aspx[quote][b]polkadot (2/25/2013)[/b][hr]SQL_Enthusiast, I double and triple checked the book and the WHILE query in my original post is a facsimile.The where clause condition is slightly different, it excludes o1.supervisor<>o1.employee. The JOIN is on o1.employee=o2.supervisor. I'm not sure if that makes a difference....[/quote]your "where" makes query to return 0 rows, so nothing get inserted.What data you expect in a table as a result of the execution of query #2?Mon, 25 Feb 2013 10:47:04 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1423378-392-1.aspxSQL_Enthusiast, I double and triple checked the book and the WHILE query in my original post is a facsimile.The where clause condition is slightly different, it excludes o1.supervisor<>o1.employee. The JOIN is on o1.employee=o2.supervisor. I'm not sure if that makes a difference....Mon, 25 Feb 2013 10:21:37 GMTpolkadothttp://www.sqlservercentral.com/Forums/Topic1423378-392-1.aspxThere's a problem in the SELECT statement in the WHILE. In the JOIN, there's only one record where ( o1.employee = o2.supervisor ) and it is excluded by the WHERE clause. Can you check the query in the book one more time?[code="sql"] SELECT DISTINCT o1.chartdepth + 1 , o1.employee , o1.supervisor FROM #org_chart o1 JOIN #org_chart o2 ON ( o1.employee = o2.supervisor ) WHERE o1.chartdepth = ( SELECT MAX(#org_chart.chartdepth) FROM #org_chart ) AND o1.supervisor <> o1.employee[/code]Sun, 24 Feb 2013 18:37:15 GMTSQL_Enthusiasthttp://www.sqlservercentral.com/Forums/Topic1423378-392-1.aspxI’m working on the hierarchy lesson in Ken Henderson’s The Guru’s Guide to Transact-SQL. He wants to show two things. First, how to write a query that populates a temporary table (#org_chart) with all possible chartdepth levels detected in the staff table. Secondly, how to write a query that shows all the boss/subordinate relationships regardless of the levels by which they are removed from each other. But, I am having trouble with the first part, the query for building the #org_chart table. I've written it below, verbatum from book. It is syntactically correctly but when I run it adds no subsequent rows to the #org_chart table. Will someone tell me what is missing? Run in this order, the DDL, query 1, and query 2, should produce a table with 40+ rows:[code="plain"] --DDL for creating and populating staff tablecreate table staff(employee int primary key, employee_name varchar(10),supervisor int null references staff (employee))insert staff values (1, 'groucho', 1),(2, 'chico', 1),(3, 'harpo', 2),(4, 'zeppo', 2),(5, 'moe', 1),(6, 'larry', 5),(7, 'curly', 5),(8, 'shemp', 5),(9, 'joe', 8),(10, 'curly joe', 9);--query #1--establishes first 10 rows of table, with first level of chart depthselect chartdepth=1, employee=o2.employee, supervisor=o1.employeeinto #org_chartfrom staff o1 inner join staff o2 on (o1.employee=o1.supervisor)--query #2 does not work for me--it is supposed to populate #org_chart with remaining levels of chartdepth. --A while loop repeats the insert as many times as necessary to process all levels in staff tablewhile (@@rowcount > 0) begininsert #org_chart (chartdepth, employee, supervisor)select distinct o1.chartdepth+1, o2.employee, o1.supervisorfrom #org_chart o1 join #org_chart o2 on (o1.employee=o2.supervisor)where o1.chartdepth=(select max(chartdepth) from #org_chart)and o1.supervisor<>o1.employeeend--if following the execution of query #1 you run the below query about 4-5 times, it accomplishes what query #2 is supposed to be able to --accomplish with a While loopinsert into #org_chartselect distinct o1.chartdepth+1, o2.employee, o1.supervisorfrom #org_chart o1 join #org_chart o2 on (o1.employee=o2.supervisor)where o1.chartdepth=(select max(chartdepth) from #org_chart)[/code]Why is query #2 not working? Thanks.Sat, 23 Feb 2013 18:16:08 GMTpolkadot