[Solved] Iterative Query for Self-Referencing Table Combined

Creating tables and queries
Post Reply
silvrwood
Posts: 14
Joined: Sat May 19, 2018 6:57 pm

[Solved] Iterative Query for Self-Referencing Table Combined

Post by silvrwood »

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

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

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:
Hrs.JPG
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?
Last edited by silvrwood on Mon Jun 04, 2018 12:03 am, edited 1 time in total.
Apache OpenOffice 4.1.5
Win10 Home x64
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Iterative Query for Self-Referencing Table Combined with

Post by UnklDonald418 »

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.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Iterative Query for Self-Referencing Table Combined with

Post by eremmel »

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

-- 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" ) > 0

UNION 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" ) > 0

ORDER 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. 
Last edited by eremmel on Sun Jun 03, 2018 8:43 am, edited 1 time in total.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
silvrwood
Posts: 14
Joined: Sat May 19, 2018 6:57 pm

Re: Iterative Query for Self-Referencing Table Combined with

Post by silvrwood »

Here is a populated sample:
HomeschoolDB_Sample.odb
(107.08 KiB) Downloaded 271 times
Also, I got closer with what I want to do with the following:

Code: Select all

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 NULL
GROUP BY "Subject", "Level", "Category"
UNION ALL
SELECT 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 NULL
GROUP BY "Subject", "Level", "Category"
ORDER BY "Category", "Level" ASC

which gives me:
HrsGrouped.JPG
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=)
Apache OpenOffice 4.1.5
Win10 Home x64
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Iterative Query for Self-Referencing Table Combined with

Post by Villeroy »

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.
Attachments
Homeschool_Pivot.ods
(24.6 KiB) Downloaded 283 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Iterative Query for Self-Referencing Table Combined with

Post by eremmel »

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

-- Level 1
select 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 NULL
GROUP 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 ) > 0

UNION ALL 

-- Level 2
select 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.0
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
GROUP 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 ) > 0

UNION ALL 

-- Level 3
select 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 NULL
GROUP BY "p1"."subjectName", "p2"."subjectName", "p3"."subjectName"
HAVING SUM( CASE WHEN "a3"."activityDuration" IS NULL 
          THEN 0 ELSE "a3"."activityDuration" END ) > 0

ORDER BY 2,1,5
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
silvrwood
Posts: 14
Joined: Sat May 19, 2018 6:57 pm

Re: Iterative Query for Self-Referencing Table Combined with

Post by silvrwood »

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.
Apache OpenOffice 4.1.5
Win10 Home x64
silvrwood
Posts: 14
Joined: Sat May 19, 2018 6:57 pm

Re: Iterative Query for Self-Referencing Table Combined with

Post by silvrwood »

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. :bravo: 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

SELECT "p1"."subjectName" AS "Hierarchy", "p1"."subjectName" AS "Subject", 
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 NULL
GROUP 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 ) > 0

UNION ALL

-- Level 2
SELECT "p1"."subjectName" + ': ' + "p2"."subjectName" AS "Hierarchy", "p2"."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.0
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
GROUP 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 ) > 0

UNION ALL

-- Level 3
SELECT "p1"."subjectName" + ': ' + "p2"."subjectName" + ': ' + "p3"."subjectName" AS "Hierarchy", "p3"."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 NULL
GROUP BY "p1"."subjectName", "p2"."subjectName", "p3"."subjectName"
HAVING SUM( CASE WHEN "a3"."activityDuration" IS NULL
THEN 0 ELSE "a3"."activityDuration" END ) > 0

ORDER 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

-- Level 1
select 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 NULL
GROUP 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 ) > 0

UNION ALL 

-- Level 2
select 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.0
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
GROUP 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 ) > 0

UNION ALL 

-- Level 3
select 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 NULL
GROUP BY "p1"."subjectName", "p2"."subjectName", "p3"."subjectName"
HAVING SUM( CASE WHEN "a3"."activityDuration" IS NULL 
          THEN 0 ELSE "a3"."activityDuration" END ) > 0

ORDER BY 2,1,5
Last edited by silvrwood on Mon Jun 04, 2018 12:02 am, edited 1 time in total.
Apache OpenOffice 4.1.5
Win10 Home x64
silvrwood
Posts: 14
Joined: Sat May 19, 2018 6:57 pm

Re: Iterative Query for Self-Referencing Table Combined with

Post by silvrwood »

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.
Apache OpenOffice 4.1.5
Win10 Home x64
Post Reply