我使用以下模式来存储节点层次结构和角色对节点的访问。角色从父节点的访问集继承对子节点的访问，除非它在accessdetails表中有条目。
我想获取给定角色的特定节点的子代、级别和访问权限。在本例中，角色为1，父级为“b”。
sqlfiddle链接

CREATE TABLE hierarchy
    (parent varchar(1), node varchar(1))
;

CREATE TABLE accessdetails
    (role integer, node varchar(1), access integer)
;

INSERT INTO hierarchy
    (parent, node)
VALUES
    (NULL, 'a'),
    ('a', 'b'),
    ('b', 'c'),
    ('c', 'p'),
    ('p', 'q'),
    ('q', 'r'),
    ('b', 'd'),
    ('d', 'j'),
    ('a', 'e'),
    ('e', 'f'),
    ('f', 'x')
;

/*
0-no,1-r,2-w,3-full

* /

insert into accessdetails
  (role, node, access)
values
  (1, 'b', 3),
  (1, 'c', 2),
  (1, 'p', 0)
 ;

以下查询正确返回级别和子代，但无法获得正确的访问权限。

with recursive
descendants as
  ( select parent, node as descendant, 1 as level,
     (select access from accessdetails where node=hierarchy.node and role=1) as access
    from hierarchy where parent = 'b'
  union all
    select d.parent, s.node, d.level + 1, 
   (select access from accessdetails where node=s.node and role=1) as access
    from descendants as d
      join hierarchy s
        on d.descendant = s.parent
  ) 
select descendant, level, access 
from descendants
order by parent, level, descendant ;

Current output:
descendant  level   access
c           1   2
d           1   (null)
j           2   (null)
p           2   0
q           3   (null)
r           4   (null)
Expected output:
descendant  level   access
c           1   2
d           1   3
j           2   3
p           2   0
q           3   0
r           4   0

我如何做到这一点？
对模式稍作修改的最终解决方案：