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
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 the difference between PL/SQL and T-SQL?
- Trouble with a query
- Grant permissions on all service broker objects
- 'New' Button is Missing from Visual Studio's Connection Manager OLE DB Destination Editor
- I have to split a string with '&' separated values without the use of a function or stored proc?
- SQL Server : large data import with clustered index
- How to create a select for each row of table in ms sql server?
- SELECT returns DataRow twice
- How do you calculate data completeness for multiple tables based on null values within columns?
- How to get the maximum row for each group of key values in a single table?
- Exec stored procedure with select as parameter
- SQL Server: where clause is being ignored
- Query showing records that do NOT match in between tables
- Cross-Database information_schema Joins in SQL Server
- T-SQL UPDATE statement affects less records than select statement
- SQL Parameters in C# aren't working as expected
- Update detail dates based on datediff from other table
- how do i return only a part of value in a row using sql
- FTS in sql 2005
- DbContext and Connection pools
- Unpivot table in MySQL
- SQL - Update table after Turning a Comma Separated string into individual rows
- Insert trigger in SQL Server
- How to speed up XQuery-modify?
- sql server 2008 how do i disconnect everyone from my db?
- SQL Server - Set and use variable within select statement
- Performance of reading varchar from SQL server using pymssql
- How to get the occurrence frequency of SQL result?
- How to populate a combobox with SQL Server stored procedure
- Export multiple stored procedures to text files
- SQL Server LIMIT 1 and LIMIT 1,1 syntax error
- Increment row number based on value 1 and value 2
- Define what indexing data structure/algorithm SQL Server uses?
- FOR JSON PATH vs FOR JSON AUTO SQL Server
- Multiple Database Access with Delphi
- cannot access Postgres views from SSIS thru ODBC PSQL driver
- SQL using UPDLOCK in query to update top 1 record after filtering and ordering table
- SQL Server AVG and Excel AVERAGE producing different results?
- What should I use for a default value for a json column in SQL Server?
- How to take first 4 time for each person
- T-SQL | Counts and ranks
- Error when running SQL stored procedure coversion failed when converting date and/or time from character string
- SQL Server 2008 100% CPU but not using a fraction of the memory it can
- SQL Server Data Tools: Error loading custom DeploymentPlanModifier: Required contributor with id could not be loaded
- Returning an Access recordset with zeros instead of nulls
- Confusing with Having query in sql
- SQL - Getting the sum of times between multiple ranges
- Declaring a variable in TSQL using CAST and GETUTCDATE() ... What is the most efficient way?
- How to restrict the length of INTEGER when creating a table in SQL Server?
- Logging into local SQL Server instance with windows auth
- Linked Server Error using MSDTC
- Foreign key in one table have high read performance or another table with two primary key?
- SQL Server 2008 R2, Function POWER different results in SQL SERVER compared to Oracle, calculator HP 12c and Excel
- Creating and Selecting table variables dynamically in SQL Server stored procedure?
- T-sql putting values of column into one row
- T-SQL fill database by procedure from another database
- Showing two temp tables data in a single table
- SQL- Extracting Text between characters
- Linq to SQL with Sproc and Variable Columns?
- How to do conditional update on columns using CTE?
- SQL Server query for total dollar amount of orders per customerID
- How to use CASE WHEN function in SQL Server
- Trouble displaying decimal points with CONVERT function in SQL Server
- Multiple Include/ThenInclude causing duplicates in EF Core
- How to correctly and efficiently reuse a prepared statement in C# .NET (SQL Server)?
- What is the fast way to update with 100 millions+ of records?
- SQLSTATE Adaptive Server connection failed (severity 9) error on Ubuntu Linux VPS
- How can I remove empty lines in SQL Server Management Studio (SSMS)?
- Optional where clause / parameter in a SQL 2008 stored proc?
- Simple Database Table Design/Layout
- SqlTableDependency for nodejs?
- SQL SERVER PIVOT like PEPSI comes under year then by months etc.. (refer attached image for a clear idea)
- Calling oracle package from SSRS
- Protect sensitive information from the DBA in SQL Server 2008
- Recursive CTE for parent and child relationship in SQL Server
- Get row count of all tables in database: SQL Server
- Create IN subquery from a VARCHAR parameter with comma separated values
- Map two source to the destination SQL DB table
- Prepared data with swapping or multiple joins each time?
- SQL-92 Query to find earliest date dependent on column value changing
- SQL Count by Active Date
- Indexing SQL-database slow down inserts too much
- Column "" cannot convert between unicode and non-unicode string data types
- Update SQL LocalDB without overwriting data
- Why the records are not getting pickets for the entry date?
- How to block certain SQL queries to be executed on the SQL Server 2008
- SQL Server Database analysis using T-SQL
- The transaction associated with the current connection has completed but has not been disposed. (edit #3)
- Selecting only rows with certain number from data table
- Cloning tree structure with guid
- SQL distinct query over two tables
- Duplicates without using While or Cursor in T-SQL
- SQL Server : join on 4-5 tables with different structures
- What is equivalent statement of DBCC INPUTBUFFER(@@SPID)(which give sql statement for current connection otr specified connection) in MYSQL?
- SQL EXEC(@QUERY) does not have access to global variables
- T-SQL Parameters
- SQL: Converting a Sub-query to a Common Table Expression
- How to SUM Grouped and Counted Field
- Scope of temporary tables in SQL Server
- Dynamic role based auth filters in EF context
- How to edit SSMS Script Table As templates?
- How can I modify this SQL query to exclude all results except from the previous two hours?
- How to get Count and Sum based on ID
- TFS Build: Can't connect to the Database
- While loop without a counter in SQL - help
- MSSQL2000: get list of role members
- SQL Query to get recursive count of employees under each manager
- Visual Studio 2013 SSDT - Support for SQL Server 2008 R2?
- Conversion failed when converting date and/or time from character string in sql on different dates
- SQL LIKE returns wrong results and double rows
- Difference between tables where 1 to 3 relationship on key field
- Difference when casting a datetime to a decimal on different SQL servers
- Customer product sale query incorrect result in sql server 2016
- Updating 1 table from another using wheres
- Changing SQL connection information for DSN-less Access frontend
- SQL Server stored procedure / cursor
- Incorrect syntax near 'userId' on search query
- Server Error in Application
- Get most expensive and cheapest items from two tables
- How to find if a Column contains '%' in SQL Server
- Getting the most recent entry per group in a select statement
- SQL query for returning row values as columns - SQL Server 2008
- Msg 232, Level 16, State 3, Line 5 Arithmetic overflow error for type int
- How to join with results on the same line in SQL?
- Is WHERE positioned in a different position in logical query phase compared to physical query process
- Grab one row from a table
- How to present hierarchical data in SQL Server 2014
- Alter column from NVARCHAR to NBINARY
- Searching a column with comma separated values
- Can FORMSOF be nested?
- Azure Functions - how to connect to local SQL Server Express database?
- Formatting the time hh:mm:ss.0000000 to hh:mm not working
- SQL Server 2005 Express - Activty Monitor - Show details is empty, how can I tell what the SQL statement that started the process is?
- nvarchar column size impact on performance
- using Tcl with tdbc over MS SQL on Win x64
- SELECT .... WHERE something equals with Chinese characters
- How do I pass timeout parameter to SQL Database Engine
- Best way to pass a connection object among forms?
- SQL Server - What happens when a row in a table is updated?
- datawarehouse design for different types of reports
- Stored procedure with multiple selects - interaction with client tool?
- Difference in the latest and second-latest price
- How to copy SSMS connections in dropdown to a new computer?
- Have XML file need it to populatemultiple SQL Tables
- java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/db
- SUM from previous ROW values
- SQL Server: how do I find the most common string in a field?
- Is there any faster way to match records with values present in a column in another table?
- Azure - The database [NAME] has reached its size quota
- Insert multiple rows from two tables into one table