Nodes
=====
id | node_name | parent
----+------------------+--------
1 | Parent Node1 | 0
2 | Child Node 1 | 1
3 | Sub Child Node 1 | 2
4 | Leaf1 | 3
HasAncestor
===========
child | ancestor
-------+----------
2 | 1
3 | 2
3 | 1
4 | 3
4 | 2
4 | 1
SELECT Nodes.*
FROM Nodes
LEFT JOIN HasAncestor
ON Nodes.id = HasAncestor.child
WHERE Nodes.id = ?
Returns for args (3)
====================
id | node_name | parent
----+------------------+--------
1 | Parent Node1 | 0
2 | Child Node 1 | 1
3 | Sub Child Node 1 | 2
####
SELECT Nodes.*
FROM Nodes
LEFT JOIN HasAncestor
ON Nodes.id = HasAncestor.child
WHERE HasAncestor.ancestor = ?
Returns for args (2)
====================
id | node_name | parent
----+------------------+--------
3 | Sub Child Node 1 | 2
4 | Leaf1 | 3
##
##
SELECT Nodes.*
FROM Nodes
WHERE Nodes.parent = ?
Returns for args (2)
====================
id | node_name | parent
----+------------------+--------
3 | Sub Child Node 1 | 2