|
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
|
|
|
|
|
|
Appropriate response 
|
|
|
|
|
That is pretty cool. I saved that one.
|
|
|
|
|
You'd always want in in a different language and with some additional fields, but a good starting point for a good model 
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
