[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):
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:
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?
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++) */
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
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.