Get parents based on child SQL id
I have the following script in a Microsoft SQL environment:
CREATE TABLE grps
(
[id] varchar(50),
[parentid] varchar(50),
[value] varchar(50)
);
INSERT INTO grps
([id], [parentid], [value])
VALUES
('-5001', '0', null),
('-5002', '-5001', null),
('-5003', '-5002', '50'),
('-5004', '-5003', null),
('-5005', '0', null),
('-5006', '0', null),
('-5007', '0', null),
('-5008', '-5006', null);
I am trying to get parents based on child id. If the requested id is the last parent, then it should only return the last element.
Examples:
- If I ask:,
id = '-5004'
it should return('-5004', '-5003', null), ('-5003', '-5002', '50'), ('-5002', '-5001', null), ('-5001', '0', null)
- If I ask
id = '-5007'
, he must return('-5007', '0', null)
It would be great if he could list the identifier given first and the rest in an ordered tree-like fashion.
I've tried several different approaches to CTEs, but no luck unfortunately. So I am looking for some help or ideas here.
Thanks in advance.
source to share
You were on the right track with CTE. This can be done with a recursive CTE! This is what a recursive CTE looks like:
DECLARE @ID varchar(50) = '5004';
WITH CTE AS
(
--This is called once to get the minimum and maximum values
SELECT id, parentid, value
FROM grps
WHERE id= @ID
UNION ALL
--This is called multiple times until the condition is met
SELECT g.id, g.parentid, g.value
FROM CTE c, grps g
WHERE g.id= c.parentid
--If you don't like commas between tables then you can replace the 2nd select
--statement with this:
--SELECT g.id, g.parentid, g.value
--FROM CTE c
--INNER JOIN grps g ON g.id= c.parentid
--This can also be written with CROSS JOINS!
--Even though it looks more like another way of writing INNER JOINs.
--SELECT g.id, g.parentid, g.value
--FROM CTE c
--CROSS JOIN grps g
--WHERE g.id = c.parentid
)
SELECT * FROM CTE
Remember the maximum recursion is 100 unless you add option (maxrecursion 0)
to the end of the last select statement. A value of 0 means infinity, but you can also set it to whatever value you want.
Enjoy!
source to share
I try my best to give hierarchy some kind of love in the world. First, the setup:
CREATE TABLE grps
(
[id] varchar(50),
[parentid] varchar(50),
[value] varchar(50),
h HIERARCHYID NULL
);
SELECT * FROM grps
INSERT INTO grps
([id], [parentid], [value])
VALUES
('-5001', '0', null),
('-5002', '-5001', null),
('-5003', '-5002', '50'),
('-5004', '-5003', null),
('-5005', '0', null),
('-5006', '0', null),
('-5007', '0', null),
('-5008', '-5006', null);
WITH cte AS (
SELECT id ,
parentid ,
value ,
CAST('/' + id + '/' AS nvarchar(max)) AS h
FROM grps
WHERE parentid = 0
UNION ALL
SELECT child.id ,
child.parentid ,
child.value ,
CAST(parent.h + child.id + '/' AS NVARCHAR(MAX)) AS h
FROM cte AS [parent]
JOIN grps AS [child]
ON child.parentid = parent.id
)
UPDATE g
SET h = c.h
FROM grps AS g
JOIN cte AS c
ON c.id = g.id
All I am doing here is add a hierarchy column to the table definition and calculate a value for it. To define the answer to the original problem, it now looks something like this:
SELECT g.id ,
g.parentid ,
g.value ,
g.h.ToString()
FROM dbo.grps AS g
JOIN grps AS c
ON c.h.IsDescendantOf(g.h) = 1
WHERE c.id = '-5004'
To make this more efficient, you must index both id and h columns independently (that is, in separate indexes).
Also, a few notes
-
If the id columns are varchar when the data appears to be numeric, then they are suspicious at best, but more importantly, they are ineffective. If it was me, I would use int. But maybe your actual data is more messy (you have IDs like "A1234").
-
I would use
NULL
parentid instead of 0 to represent top level members (i.e. no parent). But this is more a personal choice than one that has any real performance implications.
source to share