In this part two of the SQL Server and Answers post series, we will discuss writing a sql query to get the complete organizational hierarchy based on an employeeId and How to get the All employees with their managers.
The employee table has three columns, employee ID, employee name, and managerId.
CREATE TABLE [dbo].[TblEmployeeManager]( [EmpId] [int] IDENTITY(1,1) NOT NULL, [EmployeeName] [nvarchar](max) NULL, [Manager_Id] [int] NULL, CONSTRAINT [PK_TblEmployeeManager] PRIMARY KEY CLUSTERED ( [EmpId] ASC ) ) GO SET IDENTITY_INSERT [dbo].[TblEmployeeManager] ON GO INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (1, 'Michael', 5) GO INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (2, 'Jackson', 6) GO INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (3, 'Jacob', 6) GO INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (4, 'Luke', 5) GO INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (5, 'Grayson', 8) GO INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (6, 'Gabriel', 9) GO INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (7, 'Anthony', NULL) GO INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (8, 'Lincoln', 7) GO INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (9, 'Ryan', 7) GO SET IDENTITY_INSERT [dbo].[TblEmployeeManager] OFF GO
Sql Query to get employee manager name Using Self Join
SELECT emp.EmpId EmployeeId, emp.EmployeeName EmployeeName, emp.Manager_Id ManagerId, ISNULL(mng.EmployeeName, 'No Boss') AS ManagerName FROM TblEmployeeManager emp LEFT JOIN TblEmployeeManager mng ON emp.Manager_Id = mng.EmpId
SQL query to get Employee Manager Hierarchy
To get the best out of this post. These concepts should be first understood.
We discussed these in detail in our SQL server tutorial. So if you are new to these concepts, I strongly recommend reading them first before proceeding with this post. So here’s the problem definition.
Now, if an employeeId is passed to the query, then that query should list down the entire organization hierarchy.
That is who is the manager of the employeeID that is passed and who is the manager’s manager and so on, till the full hierarchy is listed.
Let me give it a context with a few examples. So this the employee’s table and this is the organization hierarchy.
Notice that Anthony is our super boss, meaning he doesn’t have any manager. So the managerId column for Anthony is null.
And if you look at Lincoln and Ryan, the two reports directly to Anthony, meaning their manager ID column should have the employeeId of Anthony. So if you look at Lincoln and Ryan, notice the manager ID is 7, which is the employeeId of Anthony.
So now based on this table data. This is what we want to achieve for the query that we are going to write if we pass Grayson’s employee Id?.
So if we pass Grayson employee ID, then the query should return data, as you can see here, Notice that employee name and manager name. So about Grayson, we have Lincoln, Lincoln reports to Anthony. And Anthony is super boss.
So Grayson reports to Lincoln. Lincoln reports to Anthony and Anthony is our super boss.
Declare @EmpId int ; Set @EmpId = 5; WITH OrganizationemployeeCTE AS ( Select EmpId, EmployeeName, Manager_Id From TblEmployeeManager Where EmpId = @EmpId UNION ALL Select TblEmployeeManager.EmpId , TblEmployeeManager.EmployeeName, TblEmployeeManager.Manager_Id From TblEmployeeManager JOIN OrganizationemployeeCTE ON TblEmployeeManager.EmpId = OrganizationemployeeCTE.Manager_Id ) Select emp1.EmployeeName, emp2.EmployeeName as ManagerName From OrganizationemployeeCTE emp1 LEFT Join OrganizationemployeeCTE emp2 ON emp1.Manager_Id = emp2.EmpId
But look at the manager name. It’s displaying null because it doesn’t have a manager ID. But instead of that, we want this “no bass”.
And how are we going to achieve that? There are several ways, again, to do that. You can either use Coalesce function or ISNULL function. So I’m going to basically use ISNULL function.
so let’s go and execute this, so notice that we get the output that we expect.
Declare @EmpId int ; Set @EmpId = 5; WITH OrganizationemployeeCTE AS ( Select EmpId, EmployeeName, Manager_Id From TblEmployeeManager Where EmpId = @EmpId UNION ALL Select TblEmployeeManager.EmpId , TblEmployeeManager.EmployeeName, TblEmployeeManager.Manager_Id From TblEmployeeManager JOIN OrganizationemployeeCTE ON TblEmployeeManager.EmpId = OrganizationemployeeCTE.Manager_Id ) Select emp1.EmployeeName, ISNULL(emp2.EmployeeName, 'No Boss') as ManagerName From OrganizationemployeeCTE emp1 LEFT Join OrganizationemployeeCTE emp2 ON emp1.Manager_Id = emp2.EmpId
The post How to get Employee manager hierarchy in Sql ? appeared first on Software Development | Programming Tutorials.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- What is an elegant way to use a column of sql output for the tag of xml output within mssql?
- T-SQL Trigger audit for update
- SQL Function call returning unexpected result
- How I do know if SQL Server Stored Procedure that performs an Update worked?
- Use column set for ALL_SPARSE_COLUMNS in the Sql View
- Why am I getting "Invalid object name 'FinishedMappings'." here?
- exception calling ".ctor" with "2" argument(s): "setparent failed for database 'yyy' ."
- Windows Firewall dropping Packets
- Why is my cursor recording 1 extra count?
- How to check the SSIS package job results after it has completed its execution?
- Why is the CLASSPATH failing for Python but working for RazorSQL?
- Columns not showinng in DataSet for dynamic SQL
- Select a predefined string if a column contains a certain value
- a rather strange phenomenon Excel MFC ADO database programming
- SQLSRV driver vs. PDO driver for PHP with MS SQL Server
- Adding a prefix to SQL result
- Insert multiple rows of default values into a table
- HASHBYTES returns different result when master.sys.fn_varbintohexstr function is used over a pre-defined variable
- Subtract a Value from a Column and Store the results in a new Column SQL
- Calling stored procedure and getting the results back
- Sharepoint 2010 List adaptor for SSIS 2016
- SSIS DT_STR to DT_DBDATE using Derived column
- grant permission to all operations with database
- sql server function native parameter bind error
- Installing sqlserver2008 SP1, the 'select features' is empty?
- Construct values in derived column based combination of values from another columns
- pymssql/pyodbc performance (cursor.execute) is very slow when querying large SQL Server table
- TSQL Dynamic ordering of values
- Service based database vs SQL Server Compact vs LocalDB?
- Dynamic SQL - assigning a function result to a variable and using the variable in a select statement
- Can this pivot be done more efficiently?
- Transpose comma seperated data into rows - some clarification
- Select only columns from joined tables from CTE
- complex sql query many to many
- Some of the arguments have values which are not valid. - Date Issue in TSQL
- How to set up access to MSSQL 2005 Database Mail?
- Correct Procedure is not executed?
- Update new column with result set from stored procedure
- MS SQL Server - Script vs Project vs Solution
- MySQL vs Microsoft SQL
- How can I have the right format of time with SQL Server?
- Not able to delete record even if it is available in the table, [ All conditions are running perfectly except else condition]
- SQL Server - Unique index vs Unique constraint - Re. Duplicate values
- SQL - Inserting and Updating Multiple Records at Once
- ASP.NET Core - Application not connecting to database after publishing
- Trouble connectiong to SQL Server Express 2008 remotely with C# - But Windows ODBC administrator works
- Schedule job between days
- Highest occupant with his details using view & max?
- Check if (during insertion) building has always same adress
- Transaction log shipping together with backup job = conflict?
- Use STUFF with INNER JOIN Query
- .NET / Entity Framework - System.Data.UpdateException not precise enough?
- SQL Server 2008 similar to group_concat topic fill distinct colums from two separate tables
- SQL Insert Performance Improvement
- How do I implement a real time *financial* statistics engine from SQL server data for dashboard display?
- Building Report Logic on Two Datasets
- Stored proc and program are still running even though browser is closed?
- How to set up API server for multiple fetch requests of SQL Server data?
- .NET SqlClient login failure from background thread in ASP.NET WebForms application
- Using insert stored procedure with SQL Server
- SQL Server 2016 temporal tables design best practice
- Make connection to database only once on page load
- Intermittant "Server does not exist or access denied"
- Constructing a PIVOT
- SQL Server : compare data in same table
- Spring Transaction handling with EclipseLink
- SQL Server Data Archive Solution
- Using CASE statement in a Data type
- SQL Server: Create an optimized view that retrieve the most recent non null value
- SQL query multiple columns in SELECT - one needs to be DISTINCT
- How to add year and end of month in sql query
- Recursive CTE with both child and parent links
- MS SQL Calculate MODE on GROUPED data
- Invalid value for key 'applicationintent'. (System.Data)
- What's the most efficient syntax to use Merge to upsert many rows at once?
- Handle is Invalid: Calling BCP from Python using the subprocess module
- How to select data where id from a string ids?
- SSIS Web Service Task returns a file file with "<", </Value> characters
- How do I convert hh:mm:ss to hh:mm in SQL Server?
- How to access the data of a table from parent database in SQL Server
- How does a recursive CTE eliminate duplicates?
- Query table with all names in one statement
- SQL Server System View Definitions
- Getting record with max date per user and inserting it to temp table - performance issue
- Cross applying or inner joining as subquery for a TVF
- Can you mix string and object outputs in SQL JSON?
- How to check if a proposed T-SQL object name is valid or needs to be enclosed in square brackets
- Convert XML to SQL Server table
- Dividing a date value in T-SQL
- View with index causes Cannot insert duplicate key row on an update
- How to write or append to text file using sql?
- SQL syntax error in WHERE CONTAIN query
- How to get the records grouping dates for a span of 3 days in SQL?
- MS Excel - join external (SQL) data with local table (sheet)
- SQL Server Indexes Aren't Helping
- Maintaining multi-tenant shared-schema database
- SQL Server performance issues when using EXCEPT in query
- converting to date time with only date and hour passed in
- Issue with dates after publishing on azure
- How to force recompile of execution plan of a Linq to SQL query?
- How can I create an "eBay style" hierarchy of categories and sub-categories?
- Cannot open database "MyDatabaseName" requested by the login. The login failed. Login failed for user 'IIS APPPOOL\MyAppName
- SQL: Update Rows That Are Unique Based on a Column
- SQL Server conditional where clause based on declared variable
- Grouping data to create range columns
- Tsql getting depending data over multiple rows in one query
- SQL SVR 08 R2 Limit a field to specific values
- SQL query is slow comparing with time stamp
- query to segregate users based on role per project in mysql
- Inserting data into sql from csv files or insert data direct from remote computer into SQL Server
- php pdo connection to mssql instance
- how to sum a column in SQL?
- Synchronizing ETL and reads on the Data Warehouse
- Linq slow while SQL fast
- how to move data
- Deleting records by passing comma separated values SQL Server & C#
- Is Microsoft Sync Framework a good tool for distributed POS system?
- Failing to bulk insert with XML model
- How to SELECT DISTINCT on 1 key column when querying multiple columns
- How to Retrieve value from a UDT
- SQL subqueries question
- Convert row result to columns
- Query to total monthly hours for events spanning month borders
- GROUP_CONCAT for SQL Server
- How to modify existing tables for timezone addition
- Query in MSSQL that returns .mdf and .ldf filename/location for specific database?
- How to write in a single stmt to get expect result
- Working around LINQ 2 SQL Coalesce limitation
- How best to copy entire databases in MS SQL Server?
- How does SQL Server treat statements inside stored procedures with respect to transactions?
- Hierarchical numbering in SQL result
- Sql Server paging rows by offset - without 'ORDER BY'
- SQL Select Query
- SQL server XML type
- How resolve table collation mismatch problems in join and union
- Update intermediate rows
- Inventory Aging Report with Adjustment Support (FIFO)
- Use Ms SQL SERVER full text search to match words
- Deploying and publishing SQL Server dependent WPF app
- sql server convert function behave differently on different machine
- Why does this delete statement work?
- PySpark 1.5 & MSSQL jdbc
- sqlcmd not working inside inno setup even with result code equals zero
- SQL Case statement Syntax
- MS SQL Server - Frequent Deadlock Issues
- SQL Server - flag column for record with lowest value
- Connect to Windows SQL Server 2008 R2 from Rails app
- How to swap day and month SQL Server
- SQL Server XML-DML how to replace element value with value of relative xpath
- Sql Server time 1 hour behind