Difference between revisions of "GnuCash SQL Examples"
From GnuCash
MaestroGlanz (talk | contribs) (Created page with "== Introduction == == Request all child accounts of a given account == <syntaxhighlight lang="sql"> with recursive account_tree as ( select guid, parent_guid fro...") |
MaestroGlanz (talk | contribs) (→Request all child accounts of a given account) |
||
| Line 8: | Line 8: | ||
== Request all child accounts of a given account == | == Request all child accounts of a given account == | ||
| + | |||
| + | This request will result in a list of the start account and all children and grand*x*children. The snippet is based on a snippet from [https://www.sql-workbench.eu/comparison/recursive_queries.html sql-workbench]. | ||
| + | |||
<syntaxhighlight lang="sql"> | <syntaxhighlight lang="sql"> | ||
| Line 18: | Line 21: | ||
child.parent_guid | child.parent_guid | ||
from accounts as child | from accounts as child | ||
| − | join account_tree as parent on parent.guid = child.parent_guid -- the | + | join account_tree as parent on parent.guid = child.parent_guid -- the self join to the CTE builds up the recursion |
) | ) | ||
select * from account_tree; | select * from account_tree; | ||
</syntaxhighlight> | </syntaxhighlight> | ||
Revision as of 11:36, 14 March 2021
Introduction
Request all child accounts of a given account
This request will result in a list of the start account and all children and grand*x*children. The snippet is based on a snippet from sql-workbench.
with recursive account_tree as (
select guid, parent_guid
from accounts
where guid = 'Start account' -- this defines the start of the recursion
union all
select child.guid,
child.parent_guid
from accounts as child
join account_tree as parent on parent.guid = child.parent_guid -- the self join to the CTE builds up the recursion
)
select * from account_tree;
