Top most manager name with out CTE

• Sreepathi1987

  Default port

  Points: 1425

  More actions

  February 26, 2015 at 8:02 am

  #298055

  Hi,

  I need to get the Top most manager name with employee name.

  for eg:-

  epmid | empname | mgrid

  1 emp1 Null

  2 emp2 1

  3 emp3 2

  4 emp4 2

  5 emp5 3

  6 emp6 Null

  7 emp7 6

  8 emp8 7

  Need out put like

  empid | empname | mgrid | mgrName

  1 emp1 null null

  2 emp2 1 emp1

  3 emp3 1 emp1

  4 emp4 1 emp1

  5 emp5 1 emp1

  6 emp6 null null

  7 emp7 6 emp6

  8 emp8 6 emp6

  ie; emp1 and emp6 are the Top most managers

  How can get this result with out using CTE. using CTE I got. But i need with out CTE

  because there are other conditions and need to append with that queries.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 26, 2015 at 8:08 am

  #1780277

  recursive CTE is the way to go. no doubt about it, and no way around it.

  just stick the top results of your recursive query in a temp table or a view which aggregates it all up for you.

  then join your other data to the temp table/view against your other data.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Emil B

  SSCertifiable

  Points: 5568

  More actions

  February 26, 2015 at 8:27 am

  #1780285

  Looks almost exactly as the example under this link www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/[/url]

• Jeff Moden

  SSC Guru

  Points: 1004329

  More actions

  February 26, 2015 at 5:15 pm

  #1780435

  Lowell (2/26/2015)

  ---

  recursive CTE is the way to go. no doubt about it, and no way around it.

  just stick the top results of your recursive query in a temp table or a view which aggregates it all up for you.

  then join your other data to the temp table/view against your other data.

  CTE's are nearly identical to an initial SELECT followed by a WHILE Loop. Done correctly, the WHILE can sometime beat the CTE quite nicely.

  With some limits, you could also use a recursive stored procedure or UDF depending on whether or not you wanted to store the results.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1004329

  More actions

  February 26, 2015 at 5:16 pm

  #1780436

  Sreepathi1987 (2/26/2015)

  ---

  How can get this result with out using CTE. using CTE I got. But i need with out CTE

  because there are other conditions and need to append with that queries.

  Why do you think using a CTE would prevent any of that?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• manesh561

  Valued Member

  Points: 62

  More actions

  March 13, 2019 at 5:47 am

  #2024306

  Sreepathi1987 - Thursday, February 26, 2015 8:02 AM

  Hi, I need to get the Top most manager name with employee name. for eg:- epmid | empname | mgrid 1 emp1 Null 2 emp2 1 3 emp3 2 4 emp4 2 5 emp5 3 6 emp6 Null 7 emp7 6 8 emp8 7 Need out put like empid | empname | mgrid | mgrName 1 emp1 null null 2 emp2 1 emp1 3 emp3 1 emp1 4 emp4 1 emp1 5 emp5 1 emp1 6 emp6 null null 7 emp7 6 emp6 8 emp8 6 emp6 ie; emp1 and emp6 are the Top most managers How can get this result with out using CTE. using CTE I got. But i need with out CTE because there are other conditions and need to append with that queries.

  how did you got it with CTE please let me know i tried it but not getting the out put

  create table #t(eid int,ename varchar(10),mid int)

  INSERT INTO #t VALUES (1, 'Ken', NULL)
  INSERT INTO #t VALUES (2, 'Terri', 1)
  INSERT INTO #t VALUES (3, 'Robert', 1)
  INSERT INTO #t VALUES (4, 'Rob', 2)
  INSERT INTO #t VALUES (5, 'Kent', 2)
  INSERT INTO #t VALUES (6, 'Bill', 3)
  INSERT INTO #t VALUES (7, 'Ryan', 3)
  INSERT INTO #t VALUES (8, 'Dane', 5)
  INSERT INTO #t VALUES (9, 'Charles', 6)
  INSERT INTO #t VALUES (10, 'Michael', 6) ;

  WITH cte (eid,ename,mid,level)
  as
  (
      select *,1 from #t where mid is null
      union all
      select a.*,b.level + 1 from #t a
      inner join cte b on a.mid = b.eid
  )
  select * from cte

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  March 13, 2019 at 12:53 pm

  #2024358

  manesh561 - Wednesday, March 13, 2019 5:47 AM

  Sreepathi1987 - Thursday, February 26, 2015 8:02 AM

  Hi, I need to get the Top most manager name with employee name. for eg:- epmid | empname | mgrid 1 emp1 Null 2 emp2 1 3 emp3 2 4 emp4 2 5 emp5 3 6 emp6 Null 7 emp7 6 8 emp8 7 Need out put like empid | empname | mgrid | mgrName 1 emp1 null null 2 emp2 1 emp1 3 emp3 1 emp1 4 emp4 1 emp1 5 emp5 1 emp1 6 emp6 null null 7 emp7 6 emp6 8 emp8 6 emp6 ie; emp1 and emp6 are the Top most managers How can get this result with out using CTE. using CTE I got. But i need with out CTE because there are other conditions and need to append with that queries.

  how did you got it with CTE please let me know i tried it but not getting the out put

  create table #t(eid int,ename varchar(10),mid int)

  INSERT INTO #t VALUES (1, 'Ken', NULL)
  INSERT INTO #t VALUES (2, 'Terri', 1)
  INSERT INTO #t VALUES (3, 'Robert', 1)
  INSERT INTO #t VALUES (4, 'Rob', 2)
  INSERT INTO #t VALUES (5, 'Kent', 2)
  INSERT INTO #t VALUES (6, 'Bill', 3)
  INSERT INTO #t VALUES (7, 'Ryan', 3)
  INSERT INTO #t VALUES (8, 'Dane', 5)
  INSERT INTO #t VALUES (9, 'Charles', 6)
  INSERT INTO #t VALUES (10, 'Michael', 6) ;

  WITH cte (eid,ename,mid,level)
  as
  (
      select *,1 from #t where mid is null
      union all
      select a.*,b.level + 1 from #t a
      inner join cte b on a.mid = b.eid
  )
  select * from cte

  In that sample set of data, and the one in your first post, any one with a NULL value for mgrid was at the top level.   If that is always going to be true, and those are always the rows you are interested in, then you just need a very simple query that has a WHERE mgrid IS NULL.   Otherwise, you would need to have a CTE that is recursive to create the hierarchy and assign some kind of value that determines which rows are the ones you are after, and then a final SELECT that has a WHERE clause that filters to only see rows that have been properly identified.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• manesh561

  Valued Member

  Points: 62

  More actions

  March 13, 2019 at 9:07 pm

  #2024405

  sgmunson - Wednesday, March 13, 2019 12:53 PM

  manesh561 - Wednesday, March 13, 2019 5:47 AM

  Sreepathi1987 - Thursday, February 26, 2015 8:02 AM

  Hi, I need to get the Top most manager name with employee name. for eg:- epmid | empname | mgrid 1 emp1 Null 2 emp2 1 3 emp3 2 4 emp4 2 5 emp5 3 6 emp6 Null 7 emp7 6 8 emp8 7 Need out put like empid | empname | mgrid | mgrName 1 emp1 null null 2 emp2 1 emp1 3 emp3 1 emp1 4 emp4 1 emp1 5 emp5 1 emp1 6 emp6 null null 7 emp7 6 emp6 8 emp8 6 emp6 ie; emp1 and emp6 are the Top most managers How can get this result with out using CTE. using CTE I got. But i need with out CTE because there are other conditions and need to append with that queries.

  how did you got it with CTE please let me know i tried it but not getting the out put

  create table #t(eid int,ename varchar(10),mid int)

  INSERT INTO #t VALUES (1, 'Ken', NULL)
  INSERT INTO #t VALUES (2, 'Terri', 1)
  INSERT INTO #t VALUES (3, 'Robert', 1)
  INSERT INTO #t VALUES (4, 'Rob', 2)
  INSERT INTO #t VALUES (5, 'Kent', 2)
  INSERT INTO #t VALUES (6, 'Bill', 3)
  INSERT INTO #t VALUES (7, 'Ryan', 3)
  INSERT INTO #t VALUES (8, 'Dane', 5)
  INSERT INTO #t VALUES (9, 'Charles', 6)
  INSERT INTO #t VALUES (10, 'Michael', 6) ;

  WITH cte (eid,ename,mid,level)
  as
  (
      select *,1 from #t where mid is null
      union all
      select a.*,b.level + 1 from #t a
      inner join cte b on a.mid = b.eid
  )
  select * from cte

  In that sample set of data, and the one in your first post, any one with a NULL value for mgrid was at the top level.   If that is always going to be true, and those are always the rows you are interested in, then you just need a very simple query that has a WHERE mgrid IS NULL.   Otherwise, you would need to have a CTE that is recursive to create the hierarchy and assign some kind of value that determines which rows are the ones you are after, and then a final SELECT that has a WHERE clause that filters to only see rows that have been properly identified.

  please help me with the query please

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  March 13, 2019 at 10:04 pm

  #2024406

  See https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017

  See Example D.
  USE AdventureWorks2012;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, EmployeeLevel)
AS (   SELECT ManagerID,
                          EmployeeID, 
                          Title, 
                           0 AS EmployeeLevel  
            FROM dbo.MyEmployees   
            WHERE ManagerID IS NULL   
            UNION ALL   
            SELECT e.ManagerID, 
                           e.EmployeeID, 
                           e.Title,
                           EmployeeLevel + 1   
            FROM dbo.MyEmployees AS e    
            INNER JOIN DirectReports AS d    
                ON e.ManagerID = d.EmployeeID ) 
           SELECT ManagerID, EmployeeID, Title, EmployeeLevel 
           FROM DirectReports
          ORDER BY ManagerID;

  If you only wanted the very top level, you'd just filter for EmployeeLevel = 1
  

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  March 14, 2019 at 1:23 pm

  #2024490

  manesh561 - Wednesday, March 13, 2019 9:07 PM

  sgmunson - Wednesday, March 13, 2019 12:53 PM

  manesh561 - Wednesday, March 13, 2019 5:47 AM

  Sreepathi1987 - Thursday, February 26, 2015 8:02 AM

  Hi, I need to get the Top most manager name with employee name. for eg:- epmid | empname | mgrid 1 emp1 Null 2 emp2 1 3 emp3 2 4 emp4 2 5 emp5 3 6 emp6 Null 7 emp7 6 8 emp8 7 Need out put like empid | empname | mgrid | mgrName 1 emp1 null null 2 emp2 1 emp1 3 emp3 1 emp1 4 emp4 1 emp1 5 emp5 1 emp1 6 emp6 null null 7 emp7 6 emp6 8 emp8 6 emp6 ie; emp1 and emp6 are the Top most managers How can get this result with out using CTE. using CTE I got. But i need with out CTE because there are other conditions and need to append with that queries.

  how did you got it with CTE please let me know i tried it but not getting the out put

  create table #t(eid int,ename varchar(10),mid int)

  INSERT INTO #t VALUES (1, 'Ken', NULL)
  INSERT INTO #t VALUES (2, 'Terri', 1)
  INSERT INTO #t VALUES (3, 'Robert', 1)
  INSERT INTO #t VALUES (4, 'Rob', 2)
  INSERT INTO #t VALUES (5, 'Kent', 2)
  INSERT INTO #t VALUES (6, 'Bill', 3)
  INSERT INTO #t VALUES (7, 'Ryan', 3)
  INSERT INTO #t VALUES (8, 'Dane', 5)
  INSERT INTO #t VALUES (9, 'Charles', 6)
  INSERT INTO #t VALUES (10, 'Michael', 6) ;

  WITH cte (eid,ename,mid,level)
  as
  (
      select *,1 from #t where mid is null
      union all
      select a.*,b.level + 1 from #t a
      inner join cte b on a.mid = b.eid
  )
  select * from cte

  In that sample set of data, and the one in your first post, any one with a NULL value for mgrid was at the top level.   If that is always going to be true, and those are always the rows you are interested in, then you just need a very simple query that has a WHERE mgrid IS NULL.   Otherwise, you would need to have a CTE that is recursive to create the hierarchy and assign some kind of value that determines which rows are the ones you are after, and then a final SELECT that has a WHERE clause that filters to only see rows that have been properly identified.

  please help me with the query please

  In what way?  Do you not know how to just query your dataset with a simple WHERE clause, as I outlined?

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• jcelko212 32090

  SSCrazy Eights

  Points: 9303

  More actions

  March 15, 2019 at 10:47 am

  #2024614

  Sreepathi1987 - Thursday, February 26, 2015 8:02 AM

  You have used a classic adjacency list model for your hierarchy. This is not the relational approach, and you need to stop what you're doing. Your bad design has made this problem needlessly hard. In fact, because it's essentially a procedural traversal approach, it's practically forcing you into a recursive CTE instead of a set oriented declarative solution.

  I've written a whole book on how to represent trees and hierarchies in SQL. What you want to do is use the nested sets model. You can Google it and find plenty of references on the Internet.

  Please post DDL and follow ANSI/ISO standards when asking for help.