Android Question Complicate SQL question

[pliroforikos]

Hello
I need to make a teacher school program. So i made a database which schema shows bellow. I know that database could be simpler but i need this schema for second project.

Next i need to fill a B4XTable with something like this

The first line is fields names. I wonder if we can do it with sql statements.
Thank you all

 

[Jeffrey Cameron]

What flavor of SQL? MS (T-SQL)? SQLite? MySQL?

 

[pliroforikos]

What flavor of SQL? MS (T-SQL)? SQLite? MySQL?

Sorry, sqlite

 

[Jeffrey Cameron]

Sorry, sqlite

Ok, my first choice would be with a CTE in T-SQL, that would be most efficient I think. For SQLite a brute-force method such as this should work assuming your SUBCLASS table is the pivot point for the other tables:

SELECT SUBC.C_ID, H.FROM, H.TO, SUBC.Number, LES.Name AS LessonName, DAYS.ID AS DayID, DAYS.NAME AS DayName
    FROM SUBCLASS AS SUBC
    LEFT JOIN SCHEDULE AS SCH ON SCH.Sub_Id = SUBC.Id
    LEFT JOIN HOURS AS H ON H.ID = SCH.Hour_Id
    LEFT JOIN LESSONS AS LES ON LES.ID = SCH.Les_Id
    LEFT JOIN DAYS ON DAYS.Id = SCH.Day_Id

If your SCHEDULE table is actually the pivot point then you'd need to modify this to select from it rather than SUBCLASS, and obviously add in whatever WHERE clause you need to filter the results to your liking (which may involve joining in more tables to achieve the filter results)

 

[pliroforikos]

Thank you very much, i'll check it as soon as possible and i'll tell you the results.

 

[emexes]

I need to make a teacher school program. So i made a database which schema shows bellow. I know that database could be simpler but i need this schema for second project.

You might (or might not ?) want to consider how to show/handle:
- non-weekly schedules eg fortnightly
- days that finish early (or more generally: different timeslots for different days)
- non-lesson scheduled items: homegroup/pastoral times, weekly assembly, recess/lunch breaks, before/after school care, free/study periods, sports days, orchestra rehearsals, orchestra performances, excursions, parent-teacher meetings

Also, it looks like you're looking to handle multiple schools in the one database, and I'm wondering if the sc_id currently in SCHEDULE might be better in SUBCLASS (if SCHOOL means school campus) or CLASS (if SCHOOL means school department/area-of-study that applies across all campuses)

I like that CLASS.name is freeform text and not some short obscure code; my son's school has an almost-unreadable timetable.

 

[pliroforikos]

You might (or might not ?) want to consider how to show/handle:
- non-weekly schedules eg fortnightly
- days that finish early (or more generally: different timeslots for different days)
- non-lesson scheduled items: homegroup/pastoral times, weekly assembly, recess/lunch breaks, before/after school care, free/study periods, sports days, orchestra rehearsals, orchestra performances, excursions, parent-teacher meetings

Unfortunately we dont have other activities in Greek public schools. So the program is like this from 8:00 to 14:00
A school teacher could teach in One to five or six schools ?. Thats why i need school name!