Page 1 of 1

### [Solved] Iterative Query for Self-Referencing Table Combined

Posted: Sat Jun 02, 2018 1:23 am
I have 3 tables involved in a query/report I would like to construct. The tables and pertinent fields are:

activities (Relation)
activityID
activityLesson (FK lessons: lessonID)
activityDuration

lessons (Relation)
lessonID
lessonSubject (FK subjects: subjectID)

subjects (Relation)
subjectID
subjectName
parentID (FK subjects: subjectID)

As you can see, subjects is a self-referencing table.

I am trying to output the content so that it looks something like this:

(Level) Subject - Parent: Hours

(1) Math - : 5
(2) Algebra - Math: 3
(2) Arithmetic - Math: 1
(1) Social Sciences - : 4
(2) History - Social Sciences: 3
(2) Social Studies - Social Sciences: 1

That is, I want to output first the parent subject with the combined total of hours from activities recorded using lessons for which the subject is either the subject of the lesson or the parent of the subject of the lesson, and then I want to output the total hours of lessons for each of the child subjects before moving on to the next parent subject.

If I were doing this via an HTML form connecting to a SQL server through PHP, I might do something like the following (not in PHP, just a sketch):

Code: Select all   Expand viewCollapse view
`VAR x = 0;VAR y = 0;VAR ParentHrs = 0;VAR Limit = 0;VAR Report = [];VAR Level = "";VAR Start = 0;VAR End = 0;VAR i = 0;VAR j = 0;document.write("Level" + "\t" + "Subject" + "\t" + "Parent" + "\t" + "Hours");Limit = SELECT COUNT(*) FROM subjects;FOR (x = 0; x < Limit; x++) {/* Determine the output starting point */Start = Report.length;/* First get all the top level parent subjects */IF ("s"."subjectParent" IS NULL) THEN {/* For each subject get itself and any child subjects */   FOR (y = 0; y < Limit; y++) {IF ("s"."subjectID" = x OR "s"."subjectParent" = x) THEN {SELECT "s"."subjectName" AS "Subject", "p"."subjectName" AS "Parent", SUM("a"."activityDuration"*1.00 )/60 AS "Hours"FROM "subjects" AS "s" LEFT OUTER JOIN "subjects" AS "p"ON "s"."subjectParent" = "p"."subjectID"LEFT OUTER JOIN "lessons" AS "l" ON "l"."lessonSubject" = "s"."subjectID"LEFT OUTER JOIN "activities" AS "a"ON "l"."lessonID" = "a"."activityLesson"GROUP BY "Subject";ParentHrs = ParentHrs + "Hours";/* Check whether this is a child subject */IF ("s"."subjectParent" NOT NULL) THEN {Level = "2";Report = [[Level, "Subject", "Parent", "Hours"]];}} /* END IF ("s"."subjectID" = x OR "s"."subjectParent" = x)  */} /* END FOR (y = 0; y < Limit; y++) */Level = "1";Report = [[Level, "Subject", NULL, ParentHrs]];End = Report.length;FOR (i = Start; i < End; i++) {FOR (j = 0; j < 4; j++) {document.write(Report[i][j] + "\t");}document.write("<br >");}} /* END IF ("s"."subjectParent" IS NULL)  */} /* END FOR (x = 0; x < Limit; x++)  */`

However, I am using OpenOffice Base with the native forms and database so that I can share the unpopulated database with others who do not have technical skills and will need the software to be local and easy to setup. The closest I come up with in SQL is:

Code: Select all   Expand viewCollapse view
`SELECT "s"."subjectName" AS "Subject", "p"."subjectName" AS "Parent", SUM( "a"."activityDuration" * 1.00 ) / 60 AS "Hours" FROM "subjects" AS "s" LEFT OUTER JOIN "subjects" AS "p" ON "s"."subjectParent" = "p"."subjectID" LEFT OUTER JOIN "lessons" AS "l" ON "l"."lessonSubject" = "s"."subjectID" LEFT OUTER JOIN "activities" AS "a" ON "l"."lessonID" = "a"."activityLesson" GROUP BY "Subject" HAVING SUM( "a"."activityDuration" ) > 0 ORDER BY "Parent" ASC, "Subject" ASC`

which gives me:

That is close, but the parent total does not include the totals of the child subjects, and the parent subjects are not grouped with the child subjects. I took a look at the pseudo recursive options using UNION statements, but they don't seem to group my data together any better than my simple query. Is there a way to achieve what I want to do in Base so that I can create a report from it, and if so, how do I do that?

### Re: Iterative Query for Self-Referencing Table Combined with

Posted: Sat Jun 02, 2018 4:58 pm
For forum volunteers to offer any help with a question this complex they would need to test their potential solution.
Please upload a sample database so we are all on the same page.

### Re: Iterative Query for Self-Referencing Table Combined with

Posted: Sat Jun 02, 2018 8:58 pm
I see that you have written a nice query, so it looks like you can write and understand some SQL. So I guess that you can figure out the idea behind the query I represent you. I've not tested it but it might push you in the right direction. When you need to modify it a little to make it work, please post your final solution so we can learn from it.
In the question I see two different levels, both connected by the parent relation. So I introduce two help-columns that are used to do the ordering.
Code: Select all   Expand viewCollapse view
`-- This will return all the parents with totals.select 1 as "level", "s"."subjectName" as "group"   , "s"."subjectName" as "Subject", '' as "Parent"   , SUM( "a"."activityDuration" * 1.00 ) / 60 AS "Hours"FROM "subjects" "s"   LEFT OUTER JOIN "subjects" AS "p" ON "s"."subjectParent" = "p"."subjectID"   LEFT OUTER JOIN "lessons" AS "l" ON "l"."lessonSubject" = "s"."subjectID"   LEFT OUTER JOIN "activities" AS "a" ON "l"."lessonID" = "a"."activityLesson"GROUP BY "s"."subjectName"HAVING SUM( "a"."activityDuration" ) > 0UNION ALL-- This will return all Subjects with a parent.SELECT 2, "p"."subjectName"    , "s"."subjectName", "p"."subjectName"   , SUM( "a"."activityDuration" * 1.00 ) / 60 FROM "subjects" AS "s"   INNER  JOIN "subjects" AS "p" ON "s"."subjectParent" = "p"."subjectID"   LEFT OUTER JOIN "lessons" AS "l" ON "l"."lessonSubject" = "s"."subjectID"   LEFT OUTER JOIN "activities" AS "a" ON "l"."lessonID" = "a"."activityLesson"   GROUP BY "s"."subjectName", "p"."subjectName"HAVING SUM( "a"."activityDuration" ) > 0ORDER BY 2,1,3 `

Note: This works nice with a fixed level of Parent/Child relations. In modern databases you can write this queries recursive for 'any' level. So you can streamline your PHP code as well.
 Edit: Removed typos from query, so it executes now on example database in next post, for AOO you need to run it in Direct Mode, because AOO does not like UNION ALL.

### Re: Iterative Query for Self-Referencing Table Combined with

Posted: Sat Jun 02, 2018 9:25 pm
Here is a populated sample:
HomeschoolDB_Sample.odb

Also, I got closer with what I want to do with the following:
Code: Select all   Expand viewCollapse view
`SELECT 1 AS "Level", "s"."subjectID" AS "Category", "s"."subjectName" AS "Subject", "p"."subjectName" AS "Parent", SUM("a"."activityDuration"*1.00 )/60 AS "Hours"FROM "subjects" AS "s" LEFT OUTER JOIN "subjects" AS "p"ON "s"."subjectParent" = "p"."subjectID"LEFT OUTER JOIN "lessons" AS "l" ON "l"."lessonSubject" = "s"."subjectID"LEFT OUTER JOIN "activities" AS "a"ON "l"."lessonID" = "a"."activityLesson"WHERE "s"."subjectParent" IS NULLGROUP BY "Subject", "Level", "Category"UNION ALLSELECT 2 AS "Level", "s"."subjectParent" AS "Category", "s"."subjectName" AS "Subject", "p"."subjectName" AS "Parent", SUM("a"."activityDuration"*1.00 )/60 AS "Hours"FROM "subjects" AS "s" LEFT OUTER JOIN "subjects" AS "p"ON "s"."subjectParent" = "p"."subjectID"RIGHT OUTER JOIN "lessons" AS "l" ON "l"."lessonSubject" = "s"."subjectID"RIGHT OUTER JOIN "activities" AS "a"ON "l"."lessonID" = "a"."activityLesson"WHERE "s"."subjectParent" IS NOT NULLGROUP BY "Subject", "Level", "Category"ORDER BY "Category", "Level" ASC`

which gives me:

That just leaves me to figure out how to add the sums from the children to the parent, and how to include a third level so that children of children appear under the second level. As it is, I could live with what I have, but if others have suggestions on how to achieve my goal, I would be most grateful. O=)

### Re: Iterative Query for Self-Referencing Table Combined with

Posted: Sun Jun 03, 2018 12:31 pm
I added a simplified query without grouping and without the sum to the database.
This query can be used for a pivot table. A pivot table (aka data pilot) is Calc's way to perform an aggregation query with agrregated figures for grouped fields. A pivot table can arrange group fields in rows and columns. A pivot table can insert additional columns and rows for intermediate results.
You can create a pivot table directly from any table or query of a registered database. For the attached demo I simply copied the query into the spreadsheet and created the pivot table from the spreadshet copy in order to get a stand-alone demo without registered database.

https://wiki.openoffice.org/wiki/Docume ... /DataPilot
Recent versions of https://libreoffice.org/ can build charts that are dynamically linked to pivot tables. With OpenOffice you have to adjust charts when the layout or size of the pivot table changes.

### Re: Iterative Query for Self-Referencing Table Combined with

Posted: Sun Jun 03, 2018 3:18 pm
Having a closer look to your database, I see that you have three levels. On each level there can exists a relation from "subjects" to "activities". Do you like to see in your reporting that at the top level of math both levels are summed up to a duration? That is what I did in the query listed below. First I build a single query that lists all three levels with top-down: from parent to child. The top parent can be identified by having no parent itself. Based on that query, I made three levels each summing up the levels below (and it self). Just try it your self:
The code can be optimized a little further, but that might mask the repetitive structure.
Code: Select all   Expand viewCollapse view
`-- Level 1select 1 as "level", "p1"."subjectName" + ':' as "Group"   , "p1"."subjectName" as "Subject"   , '' as "Parent"   , '' as "Parent's Parent"   , SUM( CASE WHEN "a1"."activityDuration" IS NULL          THEN 0 ELSE "a1"."activityDuration" END       + CASE WHEN "a2"."activityDuration" IS NULL           THEN 0 ELSE "a2"."activityDuration" END      + CASE WHEN "a3"."activityDuration" IS NULL           THEN 0 ELSE "a3"."activityDuration" END ) * 1.00 / 60 AS "Hours"FROM "subjects" "p1"      LEFT OUTER JOIN "lessons" AS "l1" ON "l1"."lessonSubject" = "p1"."subjectID"      LEFT OUTER JOIN "activities" AS "a1" ON "l1"."lessonID" = "a1"."activityLesson"   LEFT OUTER JOIN "subjects" AS "p2" ON "p2"."subjectParent" = "p1"."subjectID"      LEFT OUTER JOIN "lessons" AS "l2" ON "l2"."lessonSubject" = "p2"."subjectID"      LEFT OUTER JOIN "activities" AS "a2" ON "l2"."lessonID" = "a2"."activityLesson"   LEFT OUTER JOIN "subjects" AS "p3" ON "p3"."subjectParent" = "p2"."subjectID"      LEFT OUTER JOIN "lessons" AS "l3" ON "l3"."lessonSubject" = "p3"."subjectID"      LEFT OUTER JOIN "activities" AS "a3" ON "l3"."lessonID" = "a3"."activityLesson"WHERE "p1"."subjectParent" IS NULLGROUP BY "p1"."subjectName"HAVING SUM( CASE WHEN "a1"."activityDuration" IS NULL             THEN 0 ELSE "a1"."activityDuration" END      +   CASE WHEN "a2"."activityDuration" IS NULL             THEN 0 ELSE "a2"."activityDuration" END       +   CASE WHEN "a3"."activityDuration" IS NULL             THEN 0 ELSE "a3"."activityDuration" END ) > 0UNION ALL -- Level 2select 2 as "level", "p1"."subjectName" + ':' + "p2"."subjectName"   , "p2"."subjectName"   , "p1"."subjectName"   , ''    , SUM( CASE WHEN "a2"."activityDuration" IS NULL          THEN 0 ELSE "a2"."activityDuration" END        + CASE WHEN "a3"."activityDuration" IS NULL          THEN 0 ELSE "a3"."activityDuration" END ) * 1.00 / 60.0FROM "subjects" "p1"   LEFT OUTER  JOIN "subjects" AS "p2" ON "p2"."subjectParent" = "p1"."subjectID"      LEFT OUTER JOIN "lessons" AS "l2" ON "l2"."lessonSubject" = "p2"."subjectID"      LEFT OUTER JOIN "activities" AS "a2" ON "l2"."lessonID" = "a2"."activityLesson"   LEFT OUTER JOIN "subjects" AS "p3" ON "p3"."subjectParent" = "p2"."subjectID"      LEFT OUTER JOIN "lessons" AS "l3" ON "l3"."lessonSubject" = "p3"."subjectID"      LEFT OUTER JOIN "activities" AS "a3" ON "l3"."lessonID" = "a3"."activityLesson"WHERE "p1"."subjectParent" IS NULL    AND "p2"."subjectParent" IS NOT NULLGROUP BY "p1"."subjectName", "p2"."subjectName"HAVING SUM( CASE WHEN "a2"."activityDuration" IS NULL             THEN 0 ELSE "a2"."activityDuration" END       +   CASE WHEN "a3"."activityDuration" IS NULL             THEN 0 ELSE "a3"."activityDuration" END ) > 0UNION ALL -- Level 3select 3 as "level", "p1"."subjectName" + ':' + "p2"."subjectName" + ':' + "p3"."subjectName"    , "p3"."subjectName"    , "p2"."subjectName"    , "p1"."subjectName"    , SUM(  CASE WHEN "a3"."activityDuration" IS NULL           THEN 0 ELSE "a3"."activityDuration" END ) * 1.00 / 60 AS "Hours"FROM "subjects" "p1"   LEFT OUTER JOIN "subjects" AS "p2" ON "p2"."subjectParent" = "p1"."subjectID"      LEFT OUTER JOIN "lessons" AS "l2" ON "l2"."lessonSubject" = "p2"."subjectID"      LEFT OUTER JOIN "activities" AS "a2" ON "l2"."lessonID" = "a2"."activityLesson"   LEFT OUTER JOIN "subjects" AS "p3" ON "p3"."subjectParent" = "p2"."subjectID"      LEFT OUTER JOIN "lessons" AS "l3" ON "l3"."lessonSubject" = "p3"."subjectID"      LEFT OUTER JOIN "activities" AS "a3" ON "l3"."lessonID" = "a3"."activityLesson"WHERE "p1"."subjectParent" IS NULL    AND "p2"."subjectParent" IS NOT NULL    AND "p3"."subjectParent" IS NOT NULLGROUP BY "p1"."subjectName", "p2"."subjectName", "p3"."subjectName"HAVING SUM( CASE WHEN "a3"."activityDuration" IS NULL           THEN 0 ELSE "a3"."activityDuration" END ) > 0ORDER BY 2,1,5`

### Re: Iterative Query for Self-Referencing Table Combined with

Posted: Sun Jun 03, 2018 8:12 pm
Thank you to eremmel & Villeroy for giving me some of your valuable time to help me figure this out. I am going to look over the information and help you've provided over the course of this week and then get back to you.

I truly appreciate your expertise, time, and efforts. You are wonderful for sharing them with me.

### Re: Iterative Query for Self-Referencing Table Combined with

Posted: Sun Jun 03, 2018 11:57 pm
eremmel, you are fabulous! It was taking me forever to muddle through the logic to include the addition while grouping everything in its hierarchy. I am profoundly grateful for your time and effort in working out my solution. I just made it a bit more concise, and thanks to you I have exactly what I needed. I will include a comment with a thank you to your username.
Code: Select all   Expand viewCollapse view
`SELECT "p1"."subjectName" AS "Hierarchy", "p1"."subjectName" AS "Subject", SUM( CASE WHEN "a1"."activityDuration" IS NULLTHEN 0 ELSE "a1"."activityDuration" END+ CASE WHEN "a2"."activityDuration" IS NULLTHEN 0 ELSE "a2"."activityDuration" END+ CASE WHEN "a3"."activityDuration" IS NULLTHEN 0 ELSE "a3"."activityDuration" END ) * 1.00 / 60 AS "Hours"FROM "subjects" "p1"LEFT OUTER JOIN "lessons" AS "l1" ON "l1"."lessonSubject" = "p1"."subjectID"LEFT OUTER JOIN "activities" AS "a1" ON "l1"."lessonID" = "a1"."activityLesson"LEFT OUTER JOIN "subjects" AS "p2" ON "p2"."subjectParent" = "p1"."subjectID"LEFT OUTER JOIN "lessons" AS "l2" ON "l2"."lessonSubject" = "p2"."subjectID"LEFT OUTER JOIN "activities" AS "a2" ON "l2"."lessonID" = "a2"."activityLesson"LEFT OUTER JOIN "subjects" AS "p3" ON "p3"."subjectParent" = "p2"."subjectID"LEFT OUTER JOIN "lessons" AS "l3" ON "l3"."lessonSubject" = "p3"."subjectID"LEFT OUTER JOIN "activities" AS "a3" ON "l3"."lessonID" = "a3"."activityLesson"WHERE "p1"."subjectParent" IS NULLGROUP BY "p1"."subjectName"HAVING SUM( CASE WHEN "a1"."activityDuration" IS NULLTHEN 0 ELSE "a1"."activityDuration" END+   CASE WHEN "a2"."activityDuration" IS NULLTHEN 0 ELSE "a2"."activityDuration" END+   CASE WHEN "a3"."activityDuration" IS NULLTHEN 0 ELSE "a3"."activityDuration" END ) > 0UNION ALL-- Level 2SELECT "p1"."subjectName" + ': ' + "p2"."subjectName" AS "Hierarchy", "p2"."subjectName",SUM( CASE WHEN "a2"."activityDuration" IS NULLTHEN 0 ELSE "a2"."activityDuration" END+ CASE WHEN "a3"."activityDuration" IS NULLTHEN 0 ELSE "a3"."activityDuration" END ) * 1.00 / 60.0FROM "subjects" "p1"LEFT OUTER  JOIN "subjects" AS "p2" ON "p2"."subjectParent" = "p1"."subjectID"LEFT OUTER JOIN "lessons" AS "l2" ON "l2"."lessonSubject" = "p2"."subjectID"LEFT OUTER JOIN "activities" AS "a2" ON "l2"."lessonID" = "a2"."activityLesson"LEFT OUTER JOIN "subjects" AS "p3" ON "p3"."subjectParent" = "p2"."subjectID"LEFT OUTER JOIN "lessons" AS "l3" ON "l3"."lessonSubject" = "p3"."subjectID"LEFT OUTER JOIN "activities" AS "a3" ON "l3"."lessonID" = "a3"."activityLesson"WHERE "p1"."subjectParent" IS NULLAND "p2"."subjectParent" IS NOT NULLGROUP BY "p1"."subjectName", "p2"."subjectName"HAVING SUM( CASE WHEN "a2"."activityDuration" IS NULLTHEN 0 ELSE "a2"."activityDuration" END+   CASE WHEN "a3"."activityDuration" IS NULLTHEN 0 ELSE "a3"."activityDuration" END ) > 0UNION ALL-- Level 3SELECT "p1"."subjectName" + ': ' + "p2"."subjectName" + ': ' + "p3"."subjectName" AS "Hierarchy", "p3"."subjectName",SUM(  CASE WHEN "a3"."activityDuration" IS NULLTHEN 0 ELSE "a3"."activityDuration" END ) * 1.00 / 60 AS "Hours"FROM "subjects" "p1"LEFT OUTER JOIN "subjects" AS "p2" ON "p2"."subjectParent" = "p1"."subjectID"LEFT OUTER JOIN "lessons" AS "l2" ON "l2"."lessonSubject" = "p2"."subjectID"LEFT OUTER JOIN "activities" AS "a2" ON "l2"."lessonID" = "a2"."activityLesson"LEFT OUTER JOIN "subjects" AS "p3" ON "p3"."subjectParent" = "p2"."subjectID"LEFT OUTER JOIN "lessons" AS "l3" ON "l3"."lessonSubject" = "p3"."subjectID"LEFT OUTER JOIN "activities" AS "a3" ON "l3"."lessonID" = "a3"."activityLesson"WHERE "p1"."subjectParent" IS NULLAND "p2"."subjectParent" IS NOT NULLAND "p3"."subjectParent" IS NOT NULLGROUP BY "p1"."subjectName", "p2"."subjectName", "p3"."subjectName"HAVING SUM( CASE WHEN "a3"."activityDuration" IS NULLTHEN 0 ELSE "a3"."activityDuration" END ) > 0ORDER BY "Hierarchy"`

eremmel wrote:Having a closer look to your database, I see that you have three levels. On each level there can exists a relation from "subjects" to "activities". Do you like to see in your reporting that at the top level of math both levels are summed up to a duration? That is what I did in the query listed below. First I build a single query that lists all three levels with top-down: from parent to child. The top parent can be identified by having no parent itself. Based on that query, I made three levels each summing up the levels below (and it self). Just try it your self:
The code can be optimized a little further, but that might mask the repetitive structure.
Code: Select all   Expand viewCollapse view
`-- Level 1select 1 as "level", "p1"."subjectName" + ':' as "Group"   , "p1"."subjectName" as "Subject"   , '' as "Parent"   , '' as "Parent's Parent"   , SUM( CASE WHEN "a1"."activityDuration" IS NULL          THEN 0 ELSE "a1"."activityDuration" END       + CASE WHEN "a2"."activityDuration" IS NULL           THEN 0 ELSE "a2"."activityDuration" END      + CASE WHEN "a3"."activityDuration" IS NULL           THEN 0 ELSE "a3"."activityDuration" END ) * 1.00 / 60 AS "Hours"FROM "subjects" "p1"      LEFT OUTER JOIN "lessons" AS "l1" ON "l1"."lessonSubject" = "p1"."subjectID"      LEFT OUTER JOIN "activities" AS "a1" ON "l1"."lessonID" = "a1"."activityLesson"   LEFT OUTER JOIN "subjects" AS "p2" ON "p2"."subjectParent" = "p1"."subjectID"      LEFT OUTER JOIN "lessons" AS "l2" ON "l2"."lessonSubject" = "p2"."subjectID"      LEFT OUTER JOIN "activities" AS "a2" ON "l2"."lessonID" = "a2"."activityLesson"   LEFT OUTER JOIN "subjects" AS "p3" ON "p3"."subjectParent" = "p2"."subjectID"      LEFT OUTER JOIN "lessons" AS "l3" ON "l3"."lessonSubject" = "p3"."subjectID"      LEFT OUTER JOIN "activities" AS "a3" ON "l3"."lessonID" = "a3"."activityLesson"WHERE "p1"."subjectParent" IS NULLGROUP BY "p1"."subjectName"HAVING SUM( CASE WHEN "a1"."activityDuration" IS NULL             THEN 0 ELSE "a1"."activityDuration" END      +   CASE WHEN "a2"."activityDuration" IS NULL             THEN 0 ELSE "a2"."activityDuration" END       +   CASE WHEN "a3"."activityDuration" IS NULL             THEN 0 ELSE "a3"."activityDuration" END ) > 0UNION ALL -- Level 2select 2 as "level", "p1"."subjectName" + ':' + "p2"."subjectName"   , "p2"."subjectName"   , "p1"."subjectName"   , ''    , SUM( CASE WHEN "a2"."activityDuration" IS NULL          THEN 0 ELSE "a2"."activityDuration" END        + CASE WHEN "a3"."activityDuration" IS NULL          THEN 0 ELSE "a3"."activityDuration" END ) * 1.00 / 60.0FROM "subjects" "p1"   LEFT OUTER  JOIN "subjects" AS "p2" ON "p2"."subjectParent" = "p1"."subjectID"      LEFT OUTER JOIN "lessons" AS "l2" ON "l2"."lessonSubject" = "p2"."subjectID"      LEFT OUTER JOIN "activities" AS "a2" ON "l2"."lessonID" = "a2"."activityLesson"   LEFT OUTER JOIN "subjects" AS "p3" ON "p3"."subjectParent" = "p2"."subjectID"      LEFT OUTER JOIN "lessons" AS "l3" ON "l3"."lessonSubject" = "p3"."subjectID"      LEFT OUTER JOIN "activities" AS "a3" ON "l3"."lessonID" = "a3"."activityLesson"WHERE "p1"."subjectParent" IS NULL    AND "p2"."subjectParent" IS NOT NULLGROUP BY "p1"."subjectName", "p2"."subjectName"HAVING SUM( CASE WHEN "a2"."activityDuration" IS NULL             THEN 0 ELSE "a2"."activityDuration" END       +   CASE WHEN "a3"."activityDuration" IS NULL             THEN 0 ELSE "a3"."activityDuration" END ) > 0UNION ALL -- Level 3select 3 as "level", "p1"."subjectName" + ':' + "p2"."subjectName" + ':' + "p3"."subjectName"    , "p3"."subjectName"    , "p2"."subjectName"    , "p1"."subjectName"    , SUM(  CASE WHEN "a3"."activityDuration" IS NULL           THEN 0 ELSE "a3"."activityDuration" END ) * 1.00 / 60 AS "Hours"FROM "subjects" "p1"   LEFT OUTER JOIN "subjects" AS "p2" ON "p2"."subjectParent" = "p1"."subjectID"      LEFT OUTER JOIN "lessons" AS "l2" ON "l2"."lessonSubject" = "p2"."subjectID"      LEFT OUTER JOIN "activities" AS "a2" ON "l2"."lessonID" = "a2"."activityLesson"   LEFT OUTER JOIN "subjects" AS "p3" ON "p3"."subjectParent" = "p2"."subjectID"      LEFT OUTER JOIN "lessons" AS "l3" ON "l3"."lessonSubject" = "p3"."subjectID"      LEFT OUTER JOIN "activities" AS "a3" ON "l3"."lessonID" = "a3"."activityLesson"WHERE "p1"."subjectParent" IS NULL    AND "p2"."subjectParent" IS NOT NULL    AND "p3"."subjectParent" IS NOT NULLGROUP BY "p1"."subjectName", "p2"."subjectName", "p3"."subjectName"HAVING SUM( CASE WHEN "a3"."activityDuration" IS NULL           THEN 0 ELSE "a3"."activityDuration" END ) > 0ORDER BY 2,1,5`

### Re: Iterative Query for Self-Referencing Table Combined with

Posted: Mon Jun 04, 2018 12:02 am
Villeroy, thanks so much! This information will certainly be useful. I really appreciate your help.

Villeroy wrote:I added a simplified query without grouping and without the sum to the database.
This query can be used for a pivot table. A pivot table (aka data pilot) is Calc's way to perform an aggregation query with agrregated figures for grouped fields. A pivot table can arrange group fields in rows and columns. A pivot table can insert additional columns and rows for intermediate results.
You can create a pivot table directly from any table or query of a registered database. For the attached demo I simply copied the query into the spreadsheet and created the pivot table from the spreadshet copy in order to get a stand-alone demo without registered database.

https://wiki.openoffice.org/wiki/Docume ... /DataPilot
Recent versions of https://libreoffice.org/ can build charts that are dynamically linked to pivot tables. With OpenOffice you have to adjust charts when the layout or size of the pivot table changes.