Sql-server – Best way to implement Hierarchical data in SQL Server with multiple parents

One way to accomplish this is:

USE tempdb;
CREATE TABLE dbo.PersonHierarchy
(
    PersonHierarchyID HierarchyID
);

INSERT INTO dbo.PersonHierarchy VALUES ('/1/');
INSERT INTO dbo.PersonHierarchy VALUES ('/1/1/');
INSERT INTO dbo.PersonHierarchy VALUES ('/1/2/');
INSERT INTO dbo.PersonHierarchy VALUES ('/2/');
INSERT INTO dbo.PersonHierarchy VALUES ('/2/1/');

SELECT *
FROM dbo.PersonHierarchy;

DECLARE @T TABLE 
(
    PersonHierarchyID HierarchyID
);

INSERT INTO @T
VALUES ('/1/'), ('/2/');

WITH parent AS (
    SELECT p.PersonHierarchyID 
    FROM dbo.PersonHierarchy p
        INNER JOIN @T t ON p.PersonHierarchyID = t.PersonHierarchyID
)
SELECT ph.PersonHierarchyID.ToString(), parent.PersonHierarchyID.ToString() /* select only the necessary columns, do NOT use SELECT *  */
FROM dbo.PersonHierarchy ph
    INNER JOIN parent ON ph.PersonHierarchyID.IsDescendantOf(parent.PersonHierarchyID) = 1
ORDER BY ph.PersonHierarchyID; /* always include an ORDER BY */

Usually create a nodes table and a relationships table. Directed graphs are't really hierarchical and they can have loops which makes querying harder. But if you think of a DAG as being a generalised tree (I.e. a tree that allows multiple parents but is still strictly hierarchical) and a directed graph as being a generalised DAG (i.e. like a DAG but not strictly hierarchical) things get easier.

So for a very simple, PostgreSQL solution we might do something like:

CREATE TABLE node (
    id serial primary key,
    payload jsonb not null
);

CREATE TABLE relationship (
    id serial primary key,
    relationship_type text not null,
    from_node int references node(id) not null,
    to_node int references node(id) not null,
    payload jsonb not null
);

Then you can query something like this:

with recursive dg as (
    select n.id as node_id, null::Int as parent, array[n.id] as path
      from node n
    union all
    select to_node, from_node, path || to_node
      FROM relationship
      JOIN dg on dg.node_id = from_node AND NOT from_node = ANY(path)
)
select * from dg;