|
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[^]
|
|
|
|
|
Eddy Vluggen wrote: You'd always want in in a different language
With decades of experience with munging data from one format to another using perl that is never a problem.
Especially versus trying to get any actual business requirements out of anyone which is much harder.
|
|
|
|
|
No such thing as simple for modeling a hospital. I suggest you design out what you need and then perhaps look at examples, as posted in the first answer.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
