|
You can use a recursive CTE to traverse the employee hierarchy - there a good example on the MSDN site - Recursive Queries Using Common Table Expressions[^]
Yours is a little more complicated because you have the extra table, which could have been replaced by a single column on the Employee table "ReportsTo". It is also a little more complicated because you want the Managers in Name order followed by the Employees (for each Manager) also in Name order.
This query seems to do what you want (I've used the sample data provided in the earlier response)
;WITH emps AS
(
SELECT e.Id, e.[Name], e.[Level], er.ManagerId as ReportsTo, em.Name as ManagerName
FROM Employee e
left outer join employeeRelationship er on er.EmployeeId = e.id
left outer join employee em on er.ManagerId = em.id
), Emp_CTE AS (
SELECT Id, [Name], [Level], ReportsTo,
MtoE = CAST(CAST(isnull(ManagerName,'') AS CHAR(30)) + '/' + CAST([Name] AS CHAR(30)) AS VARCHAR(MAX))
FROM emps e
WHERE ReportsTo IS NULL
UNION ALL
SELECT e.Id, e.[Name], e.[Level], e.Id as ReportsTo,
MtoE = MtoE + '/' + CAST(e.[Name] AS CHAR(30))
FROM emps e
INNER JOIN Emp_CTE ecte ON ecte.Id = e.ReportsTo
)
SELECT Id, [Name], [Level]
FROM Emp_CTE
order by MtoE
Giving the results you asked for:
Id Name Level
3 dddd 1
9 ef 2
8 ff 2
2 nnnn 1
7 az 2
6 za 2
1 zzzz 1
5 bc 2
4 df 2
The explanation:
The first CTE emps is just to simplify the table structure as I can't use a left outer join in the recursive CTE that follows it. If you run that query by itself you get the following
Id Name Level ReportTo ManagerName
1 zzzz 1 NULL NULL
2 nnnn 1 NULL NULL
3 dddd 1 NULL NULL
4 df 2 1 zzzz
5 bc 2 1 zzzz
6 za 2 2 nnnn
7 az 2 2 nnnn
8 ff 2 3 dddd
9 ef 2 3 dddd
The second CTE Emp_CTE is a recursive query that first gets only the Managers (marked as the "Anchor member" in the comment in the code). Those results are then fed into the "Recursive member" of the CTE until all of the employees have been processed.
The "clever" bit is the derived column MtoE that "tracks" the levels of recursion, capturing first the Manager's name and then the Workers' name. I've cast the individual names to CHAR(30) to ensure that the subsequent ORDER BY works properly - i.e. making sure all of the names are the same length, padded by spaces if necessary. You might need to change the 30 to a larger number if you have some long names. Also note in the Anchor member I've cast the combination of names to be a VARCHAR as the column grows in length the further down the recursion you have to go (e.g. if there was a level 3 employee).
The very last bit just captures the data from Emp_CTE and ORDERs it BY the derived column so the output appears in name order.
|
|
|
|
|
Greetings,
I need to know how the recursive common table expression work behind the scene specifically when the recursive member returned with many result entries not a single result entry (i.e., single row) for example, the anchor member gets only one manager, and the first recursive member gets 3 employees follow that manager and they are, the 3 employees, in their turn are also managers to other employees etc. I hope that my question is clear
|
|
|
|
|
You might want to share the code for the recursive CTE that you are referring to
|
|
|
|
|
WITH TradingReceivableAccounts (AccountID, AccountCode, AccountName, GeneralAccount, AccountBranch, NatureOfAccount, AccountType, DebtorValue, CreditorValue, [Entry], [Description]) AS
(SELECT at.Account_ID,
at.Account_Code,
at.Account_Name1,
at.Account_Isleaf,
aib.AccountBranch_BranchID,
aib.AccountBranch_Natural,
aib.AccountBranch_Type,
ISNULL(aib.AccountBranch_LocalDebit, 0) AS DebtorValue,
ISNULL(aib.AccountBranch_LocalCredit, 0) AS CreditorValue,
CAST(NULL AS NVARCHAR(250)) AS [Entry],
N'TradingReceivableAccounts' AS [Description]
FROM AccountTree AS at INNER JOIN Account_InBranch AS aib
ON at.Account_ID = aib.AccountBranch_AccountID
AND aib.AccountBranch_Natural = 1
AND aib.AccountBranch_Type = 2
AND aib.AccountBranch_BranchID = 1
UNION ALL
SELECT ac.Account_ID,
ac.Account_Code,
ac.Account_Name1,
ac.Account_Isleaf,
CAST(0 AS INT) AS AccountBranch,
CAST(0 AS BIT) AS NatureOfAccount,
CAST(0 AS TINYINT) AS AccountType,
jd.JournalDet_Debit,
jd.JournalDet_Credit,
CAST(jh.Journal_Code AS NVARCHAR(250)),
N'TradingReceivableAccounts' AS [Description]
FROM TradingReceivableAccounts AS CTE INNER JOIN AccountTree AS ac
ON CTE.AccountID = ac.Account_ID
INNER JOIN Journal_Details AS jd
ON jd.JournalDet_AccountID = ac.Account_ID
INNER JOIN Journal_Head AS jh
ON jh.Journal_ID = jd.JournalDet_HeadID
AND jh.Journal_BranchID = CTE.AccountBranch
AND jh.Journal_Date < N'02/18/2016')
The above query have the same idea. The anchor member returns a specific types of financial accounts and the recursive member gets all the payments\receipts transactions associated with each account. For example, lets say the anchor query returns the accounts 1, 2, 3, and 4 and with each account returned the recursive member will return all the payments\receipts associated with the account. I need to know how it will work behind the scene.
|
|
|
|
|
You have already largely described what will happen "behind the scenes".
The Anchor member - i.e. the bit of the query after AS and before UNION ALL, is going to return a set of data in which you are interested.
The Recursive member - i.e. the bit after the UNION ALL, keeps calling itself until it has used all of the data from the anchor.
Recursion is usually used when there is a hierarchy or a sequence involved. Perhaps the simplest example I've seen is this SQL SERVER - Simple Example of Recursive CTE - Journey to SQL Authority with Pinal Dave[^]
However, looking at your query and from your description it strikes me that all you need is a simple join rather than a recursive CTE.
[EDIT] - I gave this some more thought and would like to offer this example of what is going on under the covers of an rCTE.
Imagine a simple table (I'm using a cut down version of the table in the Northwind sample database)
use Sandbox
if exists (select * from sysobjects where id = object_id('dbo.Employees') )
drop table "dbo"."Employees"
GO
CREATE TABLE "Employees" (
"EmployeeID" "int" IDENTITY (1, 1) NOT NULL ,
"LastName" nvarchar (20) NOT NULL ,
"FirstName" nvarchar (10) NOT NULL ,
"Title" nvarchar (30) NULL ,
"ReportsTo" "int" NULL
)
I've populated this with the same data as the sample database...
INSERT "Employees" VALUES('Davolio','Nancy','Sales Representative',2),
('Fuller','Andrew','Vice President, Sales',NULL),
('Leverling','Janet','Sales Representative',2),
('Peacock','Margaret','Sales Representative',2),
('Buchanan','Steven','Sales Manager',2),
('Suyama','Michael','Sales Representative',5),
('King','Robert','Sales Representative',5),
('Callahan','Laura','Inside Sales Coordinator',2),
('Dodsworth','Anne','Sales Representative',5)
And created this rCTE query to traverse the hierarchy
;WITH Emp_CTE AS (
SELECT EmployeeID, ReportsTo, LastName, FirstName, Title, 1 as RLevel
,MtoE = CAST(isnull(ReportsTo,0) AS VARCHAR(MAX)) + '/' + CAST(EmployeeID AS VARCHAR(MAX))
FROM Employees WHERE ReportsTo IS NULL
UNION ALL
SELECT e.EmployeeID, e.ReportsTo, e.LastName, e.FirstName, e.Title, RLevel + 1
,MtoE=MtoE + '/' + CAST(e.EmployeeID AS VARCHAR(MAX))
FROM Employees e
INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ReportsTo
)
SELECT EC.EmployeeID, EC.ReportsTo, EC.LastName, EC.FirstName, EC.Title, RLevel, MtoE
FROM Emp_CTE EC
If you examine the results
Emp.Id Manager LastName FirstName Job Title Level RecursionPath
2 NULL Fuller Andrew Vice President, Sales 1 0/2
1 2 Davolio Nancy Sales Representative 2 0/2/1
3 2 Leverling Janet Sales Representative 2 0/2/3
4 2 Peacock Margaret Sales Representative 2 0/2/4
5 2 Buchanan Steven Sales Manager 2 0/2/5
8 2 Callahan Laura Inside Sales Coordinator 2 0/2/8
6 5 Suyama Michael Sales Representative 3 0/2/5/6
7 5 King Robert Sales Representative 3 0/2/5/7
9 5 Dodsworth Anne Sales Representative 3 0/2/5/9 Those last two columns sort of describe the goings-on in the background. You can add something similar to your own query to see what is going on (I haven't used your query because there is no sample data for me to set up the appropriate tables)
modified 21-Feb-16 11:50am.
|
|
|
|
|
Greetings,
I am so grateful for your interesting in answering me  thanks a lot. Recall I know how the recursive CTE works behind the scene in is simplest for when the anchor query will have a single result each time, for example, Employee A is the CEO, Employee B reports to Employee A, Employee C reports to Employee B, Employee D reports to Employee C, and Employee E reports to Employee D. So the chain of management will look like:
B reports to A
C reports to B
D reports to C
E reports to D
But what if the management was looking like this: (A, B) -> (C, D, E) -> (F, G, H, I, J) where
C, D report to A
E report to B
F reports to C
G, H report to D
I, J report to E
So the first anchor member will return A and B those will be passed to the recursive member to get the C, D, and E etc. what I need to know is that is the mechanism of the recursion will go through A until it encounter NULL then go through B all way until it encounter NULL. I hope you got what I mean.
1- A, B
2- C, D
3- F
4- G, H
5- E
6- I, J
Is that how it will go?!
|
|
|
|
|
Depends on the database, Sql-server is breadth first, so it will return A,B,C,D,E,F,G,H,I,J.
Oracle on the other hand can do both Breadth first or Depth first via a parameter, and in Depth first it would return A,C,F,D,G,H,B,E,I,J.
How other databases handle things I don't know, but Breadth first is a good guess.
<edit>You can quite easy get the result you want by adding a simple order by recursionpath or order by level</edit>
modified 22-Feb-16 3:33am.
|
|
|
|
|
This is really something that you need to observe for yourself.
Try adding the following data to my sample table from my solution:
INSERT "Employees" VALUES('1Davolio','Nancy','Sales Representative',11),
('1Fuller','Andrew','Vice President, Sales',NULL),
('1Leverling','Janet','Sales Representative',11),
('1Peacock','Margaret','Sales Representative',12),
('1Buchanan','Steven','Sales Manager',11),
('1Suyama','Michael','Sales Representative',15),
('1King','Robert','Sales Representative',15),
('1Callahan','Laura','Inside Sales Coordinator',11),
('1Dodsworth','Anne','Sales Representative',15)
Run the query unchanged and observe the results:
2 NULL Fuller Andrew Vice President, Sales 1 0/2
11 NULL 1Fuller Andrew Vice President, Sales 1 0/11
10 11 1Davolio Nancy Sales Representative 2 0/11/10
12 11 1Leverling Janet Sales Representative 2 0/11/12
14 11 1Buchanan Steven Sales Manager 2 0/11/14
17 11 1Callahan Laura Inside Sales Coordinator 2 0/11/17
13 12 1Peacock Margaret Sales Representative 3 0/11/12/13
1 2 Davolio Nancy Sales Representative 2 0/2/1
3 2 Leverling Janet Sales Representative 2 0/2/3
4 2 Peacock Margaret Sales Representative 2 0/2/4
5 2 Buchanan Steven Sales Manager 2 0/2/5
8 2 Callahan Laura Inside Sales Coordinator 2 0/2/8
6 5 Suyama Michael Sales Representative 3 0/2/5/6
7 5 King Robert Sales Representative 3 0/2/5/7
9 5 Dodsworth Anne Sales Representative 3 0/2/5/9
|
|
|
|
|
Compensating unfair downvote.
|
|
|
|
|
Thank you!
Sometimes I think people think we are psychic 
|
|
|
|
|
Sometimes I think people don't think. 
|
|
|
|
|
Greetings,
Thanks a lot and as you said indeed no need to use the recursive common table expression within the code I shared and I already changed it and I used the CROSS APPLY operator and it works 100% fine and in 0 time instead of 24 seconds it was my fault what I wrote firstly . However, I've been curious on knowing how the recursive common table expression works behind the scene as I said I know how it works in its simplest form but I wanted to know what if the anchor member will return more than one result that will be joined with the recursive member and so on... how things will be done. Thanks for help now I knew what I need . Best regards, Amr Mohammad Rashad
|
|
|
|
|
i need this cod to entity
DECLARE @userTypeId BIGINT;
SET @userTypeId = 1;
WITH tblChild AS
(
SELECT *
FROM jt_PrdGroup WHERE ParentId = @userTypeId
UNION ALL
SELECT jt_PrdGroup.* FROM jt_PrdGroup JOIN tblChild ON jt_PrdGroup.ParentId = tblChild.Id
)
SELECT * FROM jt_Product p INNER JOIN (
SELECT *
FROM tblChild
UNION ALL
SELECT jt_PrdGroup.* FROM jt_PrdGroup WHERE jt_PrdGroup.Id = @userTypeId)
m1 ON m1.ID = p.Kind
|
|
|
|
|
Hi All,
I am trying to write a prototype to insert values into a Ms-Access table by using ODBC driver and ACE database engine in VC++.
software used: Ms-Access 2010(32 bit), ACE database engine(32 bit), Windows 8(64 bit), VC++ 2013 and the application is in 32 bit.
code snippet:
iRetCode = SQLAllocHandle(SQL_HANDLE_STMT, m_hdbc, &m_hstmt);
if (iRetCode != SQL_SUCCESS && iRetCode != SQL_SUCCESS_WITH_INFO)
return false;
iRetCode = SQLPrepare(m_hstmt, (SQLCHAR*)&m_sSqlStatement, SQL_NTS);
if ((iRetCode != SQL_SUCCESS && iRetCode != SQL_SUCCESS_WITH_INFO) || iRetCode != SQL_INVALID_HANDLE)
{
n = 1;
CString strMsg;
while ((retcode2 = SQLGetDiagRec(SQL_HANDLE_STMT, m_hstmt, n, SqlState, &NativeError, Msg, sizeof(Msg), &MsgLen)) != SQL_NO_DATA)
{
strMsg = Msg;
AfxMessageBox(strMsg);
n++;
}
SQLFreeHandle(SQL_HANDLE_STMT, m_hstmt);
return false;
}
later I will bind the values to the m_sSqlStatement by using SQLBindParameter(m_hstmt,,,,,,,,,)
I am using the database driver as "Driver={Microsoft Access Driver (*.mdb, *.accdb)}"
input m_sSqlStatement = "INSERT INTO LaserExportTest VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
and the SQLPrepare error: "[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'."
I tried to run the above Insert statement in Ms-Access 2010 database separately and it is able to insert values successfully. Please let me know the suggestions on insert statement that will be used in SQLPrepare statement.
Thanks in advance,
Chowdam.
|
|
|
|
|
hi, everyone
i have a program needs to build and read schema information of mysql. there are several problems i still can not figure out. I hope I can get help here. My question are:
1.what kind engines support data directory when create a table of mysql?
2.what kind engines support index directory when create a table of mysql?
3.So far i only can retrieve data directory, how to retrieve index directory?
Thanks a lot
modified 17-Feb-16 5:43am.
|
|
|
|
|
|
Hello experts,
I have build C++/MFC windows desktop applications which using MS-Access databases with ADO.
The MS-Access databases which containing data are easy to include in the setup executable.
My applications also have access to a MS-SQLServer database in a cloud location.
Now I like to replace the MS-Access databases with local MS-SQLServer database.
I only have to change the connection-strings for ADO in the applications.
The only problem I think I have:
1. How to setup a local MS-SQLServer from my setup application.
2. How to deploy a not empty MS-SQLServer database.
I don't know where to start, will you hint me in the right direction?
|
|
|
|
|
|
What is the best replacement for the MS-Access database:
SQL Server Compact or SQL Server Express?
I want a silent setup easy to do by users.
|
|
|
|
|
That is not a good reason for choice of database, you should choose whichever one fits the business requirements.
|
|
|
|
|
I need this database to contain meta-data for a language parser.
With a lexicon with 8000 words, 8000 embeded MP3 and 60 video-files.
|
|
|
|
|
Greetings experts,
when I run the following query, it correctly gives me all the records from tableA but all NULL values from tableB even though there are records in tableB.
Any ideas what I am doing wrong?
SELECT DISTINCT isnull(a.district_combo,'Not available'),
a.voter_fname,
a.voter_mname,
a.voter_lname,
a.voter_suffix,
COALESCE(a.str_number,'')+' '+COALESCE(a.str_name,'')+' '+COALESCE(a.str_suffix,'')+' '+COALESCE(a.str_apt,'') AS res_addr,
a.str_city,
a.state,
a.str_zip,
COALESCE(a.mail_str_num,'')+' '+COALESCE(a.mail_str_name,'')+' '+COALESCE(a.mail_srt_suff,'')+' '+COALESCE(a.mail_apt,'')+' '+COALESCE(a.mail_city,'')+' '+COALESCE(a.mail_state,'')+' '+COALESCE(a.mail_zip,'') AS mail_address,
a.dob,
b.* FROM dbo.tableA a LEFT OUTER JOIN dbo.tableB b ON a.record_id=b.record_id
|
|
|
|
|
If it's giving you null values from tableB, then there aren't any records in tableB which match a record in tableA using the join condition you've supplied.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
As Richard mentioned, your join is dropping them out. Which means that there is no record_id in tableB that matches record_id in tableA.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
i need a simple data warehouse for hospital
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
