-- All ancestors of '1.1.2.10'
SELECT acc.id_, s.left_ as account_, acc.name_, acc.type_ 
FROM account_struct s left join account acc on s.account_id_ = acc.id_
WHERE '1.1.2.10' BETWEEN s.left_ AND s.right_
ORDER BY s.left_;

-- All children of '1.2'
SELECT acc.id_, s.left_ as account_, acc.name_, acc.type_ 
FROM account_struct s left join account acc on s.account_id_ = acc.id_
WHERE s.left_ BETWEEN '1.2' AND '1.2Z'
ORDER BY s.left_;

-- General Balance sheet
SELECT acc.id_, ast.left_ as account_, acc.name_, (
	SELECT sum(a.balance_) 
	FROM account_struct s left join account a on s.account_id_ = a.id_
	WHERE s.left_ BETWEEN ast.left_ AND ast.right_ AND a.type_ = 'D'
) as balance_
FROM account acc left join account_struct ast on acc.id_ = ast.account_id_;

-- Balance sheet of '1.1'
SELECT acc.id_, ast.left_ as account_, acc.name_, (
	SELECT sum(a.balance_) 
	FROM account_struct s left join account a on s.account_id_ = a.id_
	WHERE s.left_ BETWEEN ast.left_ AND ast.right_ AND a.type_ = 'D'
) as balance_
FROM account acc left join account_struct ast on acc.id_ = ast.account_id_
WHERE ast.left_ BETWEEN '1.1' AND '1.1Z'
ORDER BY ast.left_;

-- Balance of '1.2.1.05' and its ancestors
SELECT acc.id_, ast.left_ as account_, acc.name_, (
	SELECT sum(a.balance_) 
	FROM account_struct s left join account a on s.account_id_ = a.id_
	WHERE s.left_ BETWEEN ast.left_ AND ast.right_ AND a.type_ = 'D'
) as balance_
FROM account acc left join account_struct ast on acc.id_ = ast.account_id_
WHERE '1.2.1.05' BETWEEN ast.left_ AND ast.right_
ORDER BY ast.left_;