locked
Hierarchy data representation under one employee RRS feed

  • Question

  • User-257070954 posted

    Hi all,

    In my application, I want to show data in hierarchy data representation. I mean if select one manager I want to see
    employees and their details under him in hierarchy way. How can we achieve this by using a hierarchy data type in SQL?
    For that what I want to pass to SQL query?

    Wednesday, November 6, 2019 8:17 AM

All replies

  • User-719153870 posted

    Hi binustrat,

    I mean if select one manager I want to see
    employees and their details under him in hierarchy way. How can we achieve this by using a hierarchy data type in SQL?
    For that what I want to pass to SQL query?

    According to your description, it seems that a one-to-many relationship exists between Manager and Employees, a manager can have multiple employees while an employee can have only one manager?

    If so, there can be two solutions:

    A simple and direct way is that you can add a ManagerID field to your Employees table, in this case, your sql query will be really simple like select * from Employees where ManagerID = 1.

    Please check below demo database structure and its query result:

    create table #Employees
    (
    EmployeeID int identity(1,1),
    EmployeeName varchar(50),
    EmployeeDetail varchar(max),
    ManagerID int
    )
    
    create table #Managers
    (
    ManagerID int identity(1,1),
    ManagerName varchar(50),
    ManagerDetail varchar(max)
    )
    
    insert #Employees values('E1','aaaaaaaa',1)
    insert #Employees values('E2','bbbbbbbb',1)
    insert #Employees values('E3','cccccccc',2)
    insert #Employees values('E4','dddddddd',2)
    insert #Employees values('E5','eeeeeeee',1)
    
    insert #Managers values('M1','AAAAAAA')
    insert #Managers values('M2','BBBBBBB')
    
    select * from #Employees
    select * from #Managers
    
    select * from #Employees where ManagerID=1

    Another way is to create a middle table Emp-Man to store the relationship between Employees and Managers. In this case, the query can be a little more complex.

    You can refer to below demo:

    create table #Employees
    (
    EmployeeID int identity(1,1),
    EmployeeName varchar(50),
    EmployeeDetail varchar(max),
    )
    
    create table #Managers
    (
    ManagerID int identity(1,1),
    ManagerName varchar(50),
    ManagerDetail varchar(max)
    )
    
    create table #EmpMan
    (
    ManagerID int,
    EmployeeID int
    )
    
    insert #Employees values('E1','aaaaaaaa')
    insert #Employees values('E2','bbbbbbbb')
    insert #Employees values('E3','cccccccc')
    insert #Employees values('E4','dddddddd')
    insert #Employees values('E5','eeeeeeee')
    
    insert #Managers values('M1','AAAAAAA')
    insert #Managers values('M2','BBBBBBB')
    
    insert #EmpMan values(1,1)
    insert #EmpMan values(1,2)
    insert #EmpMan values(1,3)
    insert #EmpMan values(2,4)
    insert #EmpMan values(2,5)
    
    select * from #Employees
    select * from #Managers
    select * from #EmpMan
    
    select em.ManagerID,e.EmployeeID,e.EmployeeName,e.EmployeeDetail from #Employees e left join #EmpMan em on e.EmployeeID=em.EmployeeID where em.ManagerID=1

    Best Regard,

    Yang Shen

    Thursday, November 7, 2019 2:29 AM
  • User-257070954 posted

    In my case, there will be many to many relations possible. And we can't use the hierarchy data type in SQL?

    Consider one more thing     : Manager -> Team-Leaders -> employees

    In This case, if I enter manager id I want to get All team leaders under him and employees under those team leaders

    Thursday, November 7, 2019 3:22 AM
  • User-719153870 posted

    Hi binustrat,

    So sorry i misunderstood your purpose, if you want to use the hierarchy data type in SQL, pleasec refer to Hierarchical Data (SQL Server).

    Also you can check below demo:

    create table #Stuff
    (
    Level hierarchyid,
    SID int identity(1,1),
    SName varchar(50),
    SDetail varchar(max),
    SType varchar(50)
    )
    
    insert into #Stuff(Level,SName,SDetail,SType) values('/','Stuff','SSSS','Stuff'),
    ('/1/','M1','MMMM','Manager'),('/2/','M2','MMMM','Manager'),
    ('/1/1/','L1','LLLL','Team-Leaders'),('/1/2/','L2','LLLL','Team-Leaders'),('/1/3/','L3','LLLL','Team-Leaders'),
    ('/2/1/','L1','LLLL','Team-Leaders'),
    ('/1/1/1/','E1','EEEE','Employee'),('/1/1/2/','E2','EEEE','Employee'),('/1/1/3/','E3','EEEE','Employee'),
    ('/1/2/1/','E4','EEEE','Employee'),('/1/2/2/','E5','EEEE','Employee'),
    ('/2/1/1/','E6','EEEE','Employee'),('/2/1/2/','E7','EEEE','Employee')
    
    select CAST(Level as varchar(100)) as [Converted Level],* from #Stuff order by Level
    select CAST(Level as varchar(100)) as [Converted Level],* from #Stuff where left(CAST(Level as varchar(100)),3)='/1/' order by Level

    Below is the result of this demo:

    Best Regard,

    Yang Shen

    Thursday, November 7, 2019 5:33 AM
  • User-257070954 posted

    As per the above query if we enter manger level, it will show all data under him. If enter team leader  i want show all employees under him. Now that's not happening. Like this all level. If one more level is there and we entering parent  we need show data under that level.  is there any way achieve this

    Friday, December 4, 2020 4:54 AM
  • User1052662409 posted

    I suggest you, go through the "Self Join" in SQL.

    Friday, December 4, 2020 7:30 AM
  • User-257070954 posted

    Can you please help me with query?

    Monday, December 7, 2020 3:54 AM