# Complete Timetable Output query

**URL:** <https://ask.gibbonedu.org/t/complete-timetable-output-query/3663>\
**Category:** Administration\
**Created:** [August 19, 2022, 4:20pm UTC](https://ask.gibbonedu.org/t/complete-timetable-output-query/3663 "2022-08-19T16:20:48Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![tiekubd](https://ask.gibbonedu.org/user_avatar/ask.gibbonedu.org/tiekubd/32/163_2.png) [@tiekubd](https://ask.gibbonedu.org/u/tiekubd)\
**Post date:** [August 19, 2022, 4:20pm UTC](https://ask.gibbonedu.org/t/complete-timetable-output-query/3663/1 "2022-08-19T16:20:48Z")

</div>

What’s the output expected with the **Complete Timetable Output** query? I have created the timetables for the current academic year, and placed classes in periods, as evidenced in Home \> Timetable \> View Master Timetable.

But when I run the query there is no output.

Thanks

Tieku

---

<div class="post-metadata">

**Author:** ![ross](https://ask.gibbonedu.org/user_avatar/ask.gibbonedu.org/ross/32/8_2.png) [@ross](https://ask.gibbonedu.org/u/ross)\
**Post date:** [August 21, 2022, 2:49am UTC](https://ask.gibbonedu.org/t/complete-timetable-output-query/3663/2 "2022-08-21T02:49:14Z")

</div>

Hi Tieku,

This query should return a row for every teacher involved in every lesson of every class taught in all timetables in the current year:

![](https://ask.gibbonedu.org/uploads/editor/7w/gcs6chboes4k.png)

However, on closer inspection I’ve realised that it is not returning rows for lessons without a facility set. I’ve now fixed this by changing JOIN gibbonSpace to LEFT JOIN gibbonSpace. You can resync your queries to enjoy this change.

Let me know if this helps!

Ross

---

<div class="post-metadata">

**Author:** ![tiekubd](https://ask.gibbonedu.org/user_avatar/ask.gibbonedu.org/tiekubd/32/163_2.png) [@tiekubd](https://ask.gibbonedu.org/u/tiekubd)\
**Post date:** [August 21, 2022, 12:31pm UTC](https://ask.gibbonedu.org/t/complete-timetable-output-query/3663/3 "2022-08-21T12:31:07Z")

</div>

Thanks Ross,

That would mean the same change should be made to query **Complete Student Timetable Output.**

Can you modify **Complete Timetable Output** so that it also includes classes with no teacher assigned? This result will help rebuild all timetables for the new year since there is no **Copy All To Next Year** feature for timetables.

Thanks

---

<div class="post-metadata">

**Author:** ![sandra](https://ask.gibbonedu.org/user_avatar/ask.gibbonedu.org/sandra/32/5_2.png) [@sandra](https://ask.gibbonedu.org/u/sandra)\
**Post date:** [August 23, 2022, 3:28am UTC](https://ask.gibbonedu.org/t/complete-timetable-output-query/3663/4 "2022-08-23T03:28:08Z")

</div>

Hi Tieku,

Any of the built in queries can be duplicated so that you can modify them for your specific needs, likely by changing the JOINS to LEFT JOINS.

---

<div class="post-metadata">

**Author:** ![tiekubd](https://ask.gibbonedu.org/user_avatar/ask.gibbonedu.org/tiekubd/32/163_2.png) [@tiekubd](https://ask.gibbonedu.org/u/tiekubd)\
**Post date:** [September 7, 2022, 9:38pm UTC](https://ask.gibbonedu.org/t/complete-timetable-output-query/3663/5 "2022-09-07T21:38:33Z")

</div>

Hi Sandra,

Yes I know I can make copies of the queries. I was asking which line to modify to include classes with no teacher assigned. Thanks

---

<div class="post-metadata">

**Author:** ![sandra](https://ask.gibbonedu.org/user_avatar/ask.gibbonedu.org/sandra/32/5_2.png) [@sandra](https://ask.gibbonedu.org/u/sandra)\
**Post date:** [September 8, 2022, 12:47am UTC](https://ask.gibbonedu.org/t/complete-timetable-output-query/3663/6 "2022-09-08T00:47:56Z")

</div>

Hi Tieku,

Something like the following should work:

```
`SELECT 
    gibbonCourse.name AS course,
    gibbonCourse.nameShort AS courseShort, 
    gibbonCourseClass.nameShort AS classShort,
    gibbonTTDay.name AS day,
    gibbonTTColumnRow.name AS period,
    substr(gibbonTTColumnRow.timeStart, 1, 5) AS timeStart,
    substr(gibbonTTColumnRow.timeEnd, 1, 5) AS timeEnd,
    gibbonSpace.name AS facility,
    gibbonPerson.surname,
    gibbonPerson.preferredName
 FROM 
    gibbonCourseClass
    JOIN gibbonCourse ON (gibbonCourseClass.gibbonCourseID=gibbonCourse.gibbonCourseID) 
    JOIN gibbonTTDayRowClass ON (gibbonTTDayRowClass.gibbonCourseClassID=gibbonCourseClass.gibbonCourseClassID)
    JOIN gibbonTTDay ON (gibbonTTDayRowClass.gibbonTTDayID=gibbonTTDay.gibbonTTDayID)
    JOIN gibbonTTColumnRow ON (gibbonTTDayRowClass.gibbonTTColumnRowID=gibbonTTColumnRow.gibbonTTColumnRowID)
    JOIN gibbonTTColumn ON (gibbonTTColumnRow.gibbonTTColumnID=gibbonTTColumn.gibbonTTColumnID)
    LEFT JOIN gibbonSpace ON (gibbonTTDayRowClass.gibbonSpaceID=gibbonSpace.gibbonSpaceID)
    LEFT JOIN gibbonCourseClassPerson ON (gibbonCourseClassPerson.gibbonCourseClassID=gibbonCourseClass.gibbonCourseClassID)
    LEFT JOIN gibbonPerson ON (gibbonCourseClassPerson.gibbonPersonID=gibbonPerson.gibbonPersonID AND gibbonCourseClassPerson.role='Teacher')
    LEFT JOIN gibbonTTDayRowClassException ON (gibbonTTDayRowClassException.gibbonTTDayRowClassID=gibbonTTDayRowClass.gibbonTTDayRowClassID)
WHERE 
    gibbonCourse.gibbonSchoolYearID=:schoolYear
    AND gibbonTTDayRowClassExceptionID IS NULL
GROUP BY gibbonTTDayRowClass.gibbonTTDayRowClassID
ORDER BY day, period, courseShort, classShort ;`
```
```