Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to create the correct structure of my sql (CTE) query, but I can not. This is a version I want to have correct version.

And this is my current version: what i have now. As you can see there are duplicates and i have no idea how to get rid of them. Futhermore the structure is wrong. I already tried to look for solutions like here but was unable to come up with solution. CTE query doesn't print the right structure?

My Employee table looks like this http://sqlfiddle.com/#!6/428d2/2

Here is my SQL query I use to create the correct name column: http://sqlfiddle.com/#!6/bf4c1/4

I use MsSQL 2012, so feel free to use newest fuctions etc. Any help appreciated.

share|improve this question
    
If you specify the root node in the CTE, it works ok. Fiddle: sqlfiddle.com/#!6/bf4c1/9 –  Phil Dec 9 '13 at 16:41
    
Hi @Phil. I thank you very much. Didn't know that at all. Thanks again. Please post your answer in "as an answer" and not a comment and will give "+". –  johnmalc Dec 9 '13 at 17:14

2 Answers 2

up vote 1 down vote accepted

If you specify the root node in the CTE, it works ok.

WITH Empl_Tab( Id ,
                 ParentId ,
                 LEVEL,
             [Order]
) AS ( SELECT Employee.[EMPl Id] ,  Employee.[reports to the Boss] ,
                0 AS LEVEL ,
              CONVERT([varchar](MAX), Employee.[EMPl Id]) AS [Order]
           FROM Employee
where [reports to the Boss] = 1
        UNION ALL

        SELECT Employee.[EMPl Id] ,
                Employee.[reports to the Boss] ,
                Empl_Tab.LEVEL+1 AS LEVEL ,
             Empl_Tab.[Order] + CONVERT([varchar](30), Employee.[EMPl Id]) AS [Order]
           FROM
                Employee INNER JOIN Empl_Tab
                ON Empl_Tab.Id = Employee.[reports to the Boss] 
     )

SELECT REPLICATE( '.' ,Empl_Tab.Id*1 )+Employee.Name AS Name
  FROM
       Employee INNER JOIN Empl_Tab
       ON Empl_Tab.Id = Employee.[EMPl Id]
  ORDER BY Empl_Tab.[Order]

SQL Fiddle here.

share|improve this answer

Looking at your code on SQLFiddle, you are on the right track with only a key point missing.

The syntax (from BOL) of a recursive CTE is :

WITH cte_name ( column_name [,...n] )
AS
(
CTE_query_definition –- Anchor member is defined.
UNION ALL
CTE_query_definition –- Recursive member is defined referencing cte_name.
)
-- Statement using the CTE
SELECT *
FROM cte_name

You are missing creating the base result set - which is the highest level in the hierarchy. This is identified by [reports to the Boss] = 1. So if you might have a CEO that does not report to anyone, he will have it set to NULL.

SELECT Employee.[EMPl Id] ,  Employee.[reports to the Boss] ,
                0 AS LEVEL ,
              CONVERT([varchar](MAX), Employee.[EMPl Id]) AS [Order]
           FROM Employee
where [reports to the Boss] = 1

The rest of the query in your fiddle looks fine.

To get you the results that you have shown, below will work:

;WITH Empl_Tab( Id ,
                 ParentId ,
                 LEVEL,
             [Order]
) AS ( SELECT Employee.[EMPl Id] ,  Employee.[reports to the Boss] ,
                0 AS LEVEL ,
              CONVERT([varchar](MAX), Employee.[EMPl Id]) AS [Order]
           FROM Employee
where [reports to the Boss] = 1
        UNION ALL

        SELECT Employee.[EMPl Id] ,
                Employee.[reports to the Boss] ,
                Empl_Tab.LEVEL+1 AS LEVEL ,
             Empl_Tab.[Order] + CONVERT([varchar](30), Employee.[EMPl Id]) AS [Order]
           FROM
                Employee INNER JOIN Empl_Tab
                ON Empl_Tab.Id = Employee.[reports to the Boss] 
     )

SELECT REPLICATE( ' ' ,Empl_Tab.Id*1 )+Employee.Name AS NAME,
        Id as EMPID,
        ParentId as SUPERVISORFK,
        POSITION
  FROM
       Employee INNER JOIN Empl_Tab
       ON Empl_Tab.Id = Employee.[EMPl Id]
  ORDER BY Empl_Tab.[Order]

enter image description here

Hope that helps to understand.

Also, look at the query that @Phil has on fiddle.

share|improve this answer
    
Thank you. Phil was a bit faster than you: ) –  johnmalc Dec 9 '13 at 17:22
1  
@johnmalc NP .. As long as we help each other, its fine. I just made sure that you understood the way CTE works as you were almost close, but missing the key point. –  Kin Dec 9 '13 at 17:23
    
Up vote for you Kin :) –  Phil Dec 9 '13 at 18:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.