October 3, 2012 at 12:01 pm
Hello Experts,
Quick question, take an example of the the following code, (may not be syntactically correct)
WITH TempCTE AS
(
SELECT ParentID, ChildID, 0 as Depth
FROM Table1
WHERE ParentID=1
UNION ALL
SELECT T1.ParentID, T1.ChildID, Depth + 1
FROM Table1 T1
INNER JOIN TempCTE T2 on T2.ChildID = T1.ParentID
)
SELECT ParentID AS ID FROM TempCTE
UNION
SELECT ChildID AS ID FROM TempCTE;
With UNION of 2 statements above, does this mean recursive being called twice or was it just one? another word where in the code that trigger the retrieval of data?
Thanks!
October 3, 2012 at 12:12 pm
With UNION of 2 statements above, does this mean recursive being called twice or was it just one?
Yes, you can check the execution plan.
another word where in the code that trigger the recursive call?
I didn't understand the question. Can you rephrase it?
October 3, 2012 at 12:14 pm
This is the "base" part of the CTE
WITH TempCTE AS
(
SELECT ParentID, ChildID, 0 as Depth
FROM Table1
WHERE ParentID=1
UNION ALL
SELECT T1.ParentID, T1.ChildID, Depth + 1
FROM Table1 T1
INNER JOIN TempCTE T2 on T2.ChildID = T1.ParentID
)
SELECT * AS ID FROM TempCTE
This is the recursive part
WITH TempCTE AS
(
SELECT ParentID, ChildID, 0 as Depth
FROM Table1
WHERE ParentID=1
UNION ALL
SELECT T1.ParentID, T1.ChildID, Depth + 1
FROM Table1 T1
INNER JOIN TempCTE T2 on T2.ChildID = T1.ParentID
)
SELECT * AS ID FROM TempCTE
EDIT: the highlight doesn't seem to be working. switching to bold instead.
I don't think you want the second UNION statement - it's going to suppress a lot of the results rendered by the recursive traversal.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 3, 2012 at 12:18 pm
haiao2000 (10/3/2012)
Hello Experts,Quick question, take an example of the the following code, (may not be syntactically correct)
WITH TempCTE AS(
SELECT ParentID, ChildID, 0 as Depth
FROM Table1
WHERE ParentID=1
UNION ALL
SELECT T1.ParentID, T1.ChildID, Depth + 1
FROM Table1 T1
INNER JOIN TempCTE T2 on T2.ChildID = T1.ParentID
)
SELECT ParentID AS ID FROM TempCTE
UNION
SELECT ChildID AS ID FROM TempCTE;
With UNION of 2 statements above, does this mean recursive being called twice or was it just one? another word where in the code that trigger the recursive call?
Thanks!
It would appear that you're using an "Adjacency List" hierarchy. With that thought in mind, why would you use the query above when, by definition, each ParentID should also be a ChildID and the ChildID must be unique.
Given those facts about an "Adjacency List" hierchy, the ae quer can be very simply replaced with...
SELECT ChildID
FROM Table1
--Jeff Moden
Change is inevitable... Change for the better is not.
October 3, 2012 at 12:20 pm
Matt Miller (#4) (10/3/2012)
This is the "base" part of the CTEWITH TempCTE AS
(
[highlight=#ffff11]SELECT ParentID, ChildID, 0 as Depth
FROM Table1
WHERE ParentID=1 [/highlight]
UNION ALL
SELECT T1.ParentID, T1.ChildID, Depth + 1
FROM Table1 T1
INNER JOIN TempCTE T2 on T2.ChildID = T1.ParentID
)
SELECT * AS ID FROM TempCTE
This is the recursive part
WITH TempCTE AS
(
SELECT ParentID, ChildID, 0 as Depth
FROM Table1
WHERE ParentID=1
UNION ALL
[highlight=#ffff11]
SELECT T1.ParentID, T1.ChildID, Depth + 1
FROM Table1 T1
INNER JOIN TempCTE T2 on T2.ChildID = T1.ParentID [/highlight]
)
SELECT * AS ID FROM TempCTE
I don't think you want the second UNION statement - it's going to suppress a lot of the results rendered by the recursive traversal.
Matt,
Actually, I know which is base and recursive parts, what I don't know is which part in that entire code triggers the retrival of data. Is it the "SELECT ParentID as ID FROM TempCTE" or is it the entire code block as a whole.
my concerns is if it is "SELECT ParentID as ID FROM TempCTE" that trigger the retrieval of data, then by doing
SELECT ParentID as ID FROM TempCTE
UNION
"SELECT ChildID as ID FROM TempCTE <-- this would cause another round of retrieving data ???
October 3, 2012 at 12:23 pm
Jeff,
Maybe that is just a quick bad example I came up with on fly, but what I meant was that table can have data like this:
parent/child
1/2
2/3
3/4
1/5...so on and so forth. which meant there are children, grandchildren, great grand children etc. all stored in one table
thanks
October 3, 2012 at 12:28 pm
haiao2000 (10/3/2012)
Matt,
Actually, I know which is base and recursive parts, what I don't know is which part in that entire code triggers the retrival of data. Is it the "SELECT ParentID as ID FROM TempCTE" or is it the entire code block as a whole.
my concerns is if it is "SELECT ParentID as ID FROM TempCTE" that trigger the retrieval of data, then by doing
SELECT ParentID as ID FROM TempCTE
UNION
"SELECT ChildID as ID FROM TempCTE <-- this would cause another round of retrieving data ???
It's more complicated than that. it's not going to trigger a second retrieval of the data, it's going to force a DISTINCT select, and you've now destroyed the structure you just built out.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 3, 2012 at 12:40 pm
Matt Miller (#4) (10/3/2012)
haiao2000 (10/3/2012)
Matt,
Actually, I know which is base and recursive parts, what I don't know is which part in that entire code triggers the retrival of data. Is it the "SELECT ParentID as ID FROM TempCTE" or is it the entire code block as a whole.
my concerns is if it is "SELECT ParentID as ID FROM TempCTE" that trigger the retrieval of data, then by doing
SELECT ParentID as ID FROM TempCTE
UNION
"SELECT ChildID as ID FROM TempCTE <-- this would cause another round of retrieving data ???
It's more complicated than that. it's not going to trigger a second retrieval of the data, it's going to force a DISTINCT select, and you've now destroyed the structure you just built out.
What if I just want the IDs for every children, grand children, great grand children, great great grand children nodes.... for a particular node? but anyway that is out of scope of what I asked, what I tried to understand is whether the code trigger more than one recursive call. That is all. Let's forget about the data or table structure as it was just something I made up to illustrate my point.
October 3, 2012 at 1:00 pm
Please take a look at what Jeff mentioned: assuming the data is structured as he mentioned (which would be standard for an adjacency), simply listing out the childID's from your CTE would give you your list (it's the top-level parent and each of its children, grandchildren etc...).
As in:
WITH TempCTE AS
(
SELECT ParentID, ChildID, 0 as Depth
FROM Table1
WHERE ParentID=1
UNION ALL
SELECT T1.ParentID, T1.ChildID, Depth + 1
FROM Table1 T1
INNER JOIN TempCTE T2 on T2.ChildID = T1.ParentID
)
SELECT ChildID AS ID FROM TempCTE;
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 3, 2012 at 1:18 pm
Now I see your point it is a really bad example I have there. My Bad.
Thanks Guys!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply