November 21, 2016 at 7:47 am
Hi, i need a query to find out all orphan over a hierarchical table
with the code above i can get all orphans of first generation, BUT i need to extrac all the records children of the orphans.
in the example the code find out correctly the record ID=5 as orphan, but if id=5 is orphan the record id=6 is orphan too because is child of 5. How to change the query to retrive all the children of the orpahans?
in this structure a root node cannot be orphan, a record is root if l1>0 and l2=0, l3=0, l4=0
Thanks.
declare @Sample table (L1 int, L2 int, L3 int, L4 int, ID int, description varchar(100))
Insert Into @Sample (l1,l2,l3,l4,Id, description) values (1,0,0,0,1, 'Root')
Insert Into @Sample (l1,l2,l3,l4,Id, description) values (1,1,0,0,2, 'first child of root')
Insert Into @Sample (l1,l2,l3,l4,Id, description) values (1,1,1,0,3, 'first child of first child of root')
Insert Into @Sample (l1,l2,l3,l4,Id, description) values (1,2,0,0,4, 'second child of root')
Insert Into @Sample (l1,l2,l3,l4,Id, description) values (2,1,0,0,5, 'orphan')
Insert Into @Sample (l1,l2,l3,l4,Id, description) values (2,1,1,0,6, 'child of 5')
Select * From
(
Select tax.ID, tax.description ,
CASEWhen tax.L4 > 0 Then 4
When tax.L3 > 0 Then 3
When tax.L2 > 0 Then 2
When tax.L1 > 0 Then 1
END AS TAXLEVEL,
CASE
When tax.L4 > 0 Then (SELECT ID FROM @Sample WHERE L1 = tax.L1 AND L2 = tax.L2 AND L3 = tax.L3 AND L4 = 0 )
When tax.L3 > 0 Then (SELECT ID FROM @Sample WHERE L1 = tax.L1 AND L2 = tax.L2 AND L3 = 0 AND L4 = 0 )
When tax.L2 > 0 Then (SELECT ID FROM @Sample WHERE L1 = tax.L1 AND L2 = 0 AND L3 = 0 AND L4 = 0 )
When tax.L1>0 Then NULL END AS PARENT
From @Sample tax
) Q
Where Parent is null and TaxLevel>1
November 21, 2016 at 8:41 am
Here's an option on how to get all the orphans and their children.
declare @Sample table (L1 int, L2 int, L3 int, L4 int, ID int, description varchar(100))
Insert Into @Sample (l1,l2,l3,l4,Id, description)
values
(1,0,0,0,1, 'Root')
,(1,1,0,0,2, 'first child of root')
,(1,1,1,0,3, 'first child of first child of root')
,(1,2,0,0,4, 'second child of root')
,(2,1,0,0,5, 'orphan')
,(2,1,1,0,6, 'child of 5');
WITH CTE AS(
SELECT o.*
FROM @Sample o
LEFT
JOIN @Sample p ON o.L1 = p.L1 AND ((o.L2 = p.L2 AND o.L3 = p.L3 AND o.L4 > 0 AND p.L4 = 0)
OR (o.L2 = p.L2 AND o.L3 > 0 AND p.L3 = 0)
OR (o.L2 > 0 AND p.L2 = 0))
WHERE o.L2 > 0
AND p.L1 IS NULL
)
SELECT *
FROM @Sample c
WHERE EXISTS(
SELECT *
FROM CTE p
WHERE c.L1 = p.L1 AND ((c.L2 = p.L2 AND c.L3 = p.L3 AND p.L4 = 0)
OR (c.L2 = p.L2 AND p.L3 = 0)
OR (p.L2 = 0)));
November 30, 2016 at 5:42 pm
>> I need a query to find out all orphans over a hierarchical table <<
``
I never heard this term before, and I wrote the standards. I think you are trying to find some trees not connected to a particular root node. Is that correct? You are trying to get all of his into one tree.
>> with the code above I can get all orphans of first generation, BUT I need to extract all the records [sic] children of the orphans. <<
Rows are nothing like records. This is a fundamental concept that should have been covered the first week of your first SQL class.
A level in a tree structure is the value an attribute of a node. Yet you model A limited run of four levels as if they were attributes and not The values of an attribute! Your data model is wrong at a fundamental level.
Google “Nested sets model”, “Celko” and “SQL” for a better approach or get a copy of my book on this topic. Orphans are not possible with it, so the problems created by Bad design do not exist..
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
December 2, 2016 at 7:04 am
CELKO (11/30/2016)
>> I need a query to find out all orphans over a hierarchical table <<``
I never heard this term before, and I wrote the standards. I think you are trying to find some trees not connected to a particular root node. Is that correct? You are trying to get all of his into one tree.
I'm sorry, i transaleted from Italian, i don't know what is the right term to indicate a node without any parent.
December 2, 2016 at 8:53 am
fabriziodb (12/2/2016)
CELKO (11/30/2016)
>> I need a query to find out all orphans over a hierarchical table <<``
I never heard this term before, and I wrote the standards. I think you are trying to find some trees not connected to a particular root node. Is that correct? You are trying to get all of his into one tree.
I'm sorry, i transaleted from Italian, i don't know what is the right term to indicate a node without any parent.
Don't worry, most people with real world knowledge can understand what you mean with orphans. Only theorists won't understand and will want to enforce their terminology.
December 2, 2016 at 12:14 pm
The collection of trees is called a forest. A single node by itself is a degenerate tree. Each of the trees in the forest will have a root, so the concept of an orphan does not really work. I think you wanted to establish a node is the root of a single tree and that for some reason you are breaking pieces of the tree off incorrectly.
You will probably find this in any textbook on graph theory. I do not know if you had time to look at the nested set model, very quickly. Here is the skeleton of a nested set model for your tree;
CREATE TABLE Forest
(node_name VARCHAR(20) NOT NULL PRIMARY KEY,
lft INTEGER NOT NULL CHECK (lft > 0),
rgt INTEGER NOT NULL CHECK (rgt > 1),
CHECK(lft < rgt));
INSERT INTO Forest
VALUES
('Root',1,8),
('first child of root',2, 5),
('first child of first child of root',3,4),
('second child of root', 6,7),
--- this has to be forced in a nested sets model
('orphan' 100,103),
('child of 5', 101, 102);
In order to create an "orphan", you have to go out of your way and force it in the nested set model. This means disjoint trees (as opposed to subtrees) will not have (lft, rgt) coordinates not between the (lft, rgt) coordinates of the second tree. You seem to want to use the tree whose root is named "root", so you would need something like this:
WITH Root_Tree (node_name,lft, rgt)
SELECT node_name, lft, rgt FROM Forest WHERE lft =1;
SELECT F.node_name, F.lft, F.rgt
FROM Forest AS F, Root_Tree AS R
WHERE F.lft NOT BETWEEN R.lft AND R.rgt
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
December 3, 2016 at 8:30 am
Luis Cazares (12/2/2016)
fabriziodb (12/2/2016)
CELKO (11/30/2016)
>> I need a query to find out all orphans over a hierarchical table <<``
I never heard this term before, and I wrote the standards. I think you are trying to find some trees not connected to a particular root node. Is that correct? You are trying to get all of his into one tree.
I'm sorry, i transaleted from Italian, i don't know what is the right term to indicate a node without any parent.
Don't worry, most people with real world knowledge can understand what you mean with orphans. Only theorists won't understand and will want to enforce their terminology.
+ 1 Billion. We know what orphans are and you made your point clear.
December 3, 2016 at 10:56 am
>> We know what orphans are and you made your point clear. <<
No, we do not. We will have to make assumptions. My guess (and it is only a guess!) That he has a forest. Remember my first Masters was in mathematics, the second Masters was in computer science and that I teach college every now and then. I have seen what happens when you have a noob who does not have the right terms or concepts.
My assumption is that there is a "root" tree in his forest and that everything is supposed to eventually be a subtree of this "root" tree. All of this is assumption because the problem was not well stated. Add to this the fact that he picked a horrible way to model his problem and you have some real trouble. I gave a single query solution, without any recursion or any need for it because the recursion is in the data structure where belongs, and not in procedural code (which we declarative language programmers hate).
My attitude toward forums and newsgroups is at one of their primary functions (other than lazy people getting their homework done, or incompetent people answering interview questions) is to educate the noobs. Education in a complex technical subject is not easy; unit of the quick answer ("smashing the screws with a big rock!") Were a more detailed answer with stuff that they can use to do their own research (" there is this tool called a screwdriver…", God bless Google).
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
December 3, 2016 at 11:31 am
There doesn't necessarily need to be a "root tree" in the forest. The landscape can easily have multiple related but still separate trees in the same table.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 5, 2016 at 11:11 am
Eventhough, I appreciate your intentions to "educate the noobs". I would suggest that you stop bashing them when you can't even test your code before posting it. The code is full of errors and uses an old syntax that is partly deprecated in SQL Server. The model is certainly wrong, but sometimes we can't change it.
A great developer (or professional) will be able to adapt to the options available.
December 6, 2016 at 10:56 am
Correct minor typos:
INSERT INTO Forest
VALUES
('Root',1,8),
('first child of root',2, 5),
('first child of first child of root',3,4),
('second child of root', 6,7),
--- this has to be forced in a nested sets model
('orphan', 100,103),
('child of 5', 101, 102);
WITH Root_Tree (node_name,lft, rgt)
AS
(SELECT node_name, lft, rgt FROM Forest WHERE lft =1)
SELECT F.node_name, F.lft, F.rgt
FROM Forest AS F, Root_Tree AS R
WHERE F.lft NOT BETWEEN R.lft AND R.rgt
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
December 6, 2016 at 5:30 pm
CELKO (12/6/2016)
Correct minor typos:CREATE TABLE Forest
(node_name VARCHAR(20) NOT NULL PRIMARY KEY,
lft INTEGER NOT NULL CHECK (lft > 0),
rgt INTEGER NOT NULL CHECK (rgt > 1),
CHECK(lft < rgt));
INSERT INTO Forest
VALUES
('Root',1,8),
('first child of root',2, 5),
('first child of first child of root',3,4),
('second child of root', 6,7),
--- this has to be forced in a nested sets model
('orphan', 100,103),
('child of 5', 101, 102);
WITH Root_Tree (node_name,lft, rgt)
AS
(SELECT node_name, lft, rgt FROM Forest WHERE lft =1)
SELECT F.node_name, F.lft, F.rgt
FROM Forest AS F, Root_Tree AS R
WHERE F.lft NOT BETWEEN R.lft AND R.rgt
still not there Joe, still untested code. it's tough shooting from the hip sometimes.
the error is as follows:
Msg 8152, Level 16, State 14, Line 7
String or binary data would be truncated.
The statement has been terminated.
(0 row(s) affected)
Lowell
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply