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