CODEWITHSUNDEEP
sqlsql-server
Gurby
Gurby

Reputation: 33

Get parents based on child id SQL

I have the following scenario 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'm trying to get parents based on the id of a child. If the id queried is the last parent then it should only return the last item.

Examples:

It would be awesome if it could list the id queried first and the rest in an orderly fashion up the "tree".

I've tried several different approaches with CTE's but with no luck unfortunately. So I'm looking for some help or ideas here.

Thanks in advance.

Upvotes: 3

Views: 3844

Answers (2)

John Odom
John Odom

Reputation: 1223

You were on the right track with CTE's. It can be done by using recursive CTE! Here is how the 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

Beware that the maximum recursion is 100 unless you add option (maxrecursion 0) to the end of the last select statement. The 0 means infinite but you can also set it to any value you want.

Enjoy!

Upvotes: 3

Ben Thul
Ben Thul

Reputation: 32687

I'm trying my best to give hierarchyid some 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'm doing here is adding a hierarchyid column to your table definition and calculating the value for it. To determine answer your original problem, now it 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 performant, you should index both the id and h columns independently (that is, in separate indexes).

Also, a couple of notes

  • Having the id columns be varchar when the data looks numeric is fishy at best, but more importantly it's inefficient. If it were me, I'd use an int. But perhaps your actual data is messier (i.e you have ids like 'A1234').

  • I'd also use NULL instead of 0 for the parentid to represent top-level (i.e. those with no parent) members. But that's more of a personal choice rather than one that has any real performance implications.

Upvotes: 2

Related Questions