Android Question Complicate SQL question

pliroforikos

pliroforikos

Active Member
Licensed User
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.
SchoolSchedule.jpeg

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
 
Sort by date Sort by votes

Jeffrey Cameron

Well-Known Member
Licensed User
Longtime User
pliroforikos said:
Sorry, sqlite
Click to expand...
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:
B4X: 
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)
 
Upvote 0
E

emexes

Expert
Licensed User
pliroforikos said:
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.
Click to expand...
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.
 
Last edited:
Upvote 0

pliroforikos

Active Member
Licensed User
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!
 
Upvote 0
You must log in or register to reply here.

Similar Threads

D
  • Question
Android Question SQL Query Question
Replies
2
Views
613
dw_b4x
D
T
  • Question
Android Question Newbie questions on IDE, Libraries and BLE
Replies
2
Views
1K
tseyfarth
T
  • Locked
  • Sticky
  • Article
Android Tutorial [B4X] "Code Smells" - common mistakes and other tips
2 3
Replies
43
Views
100K
Erel
  • Locked
  • Article
Android Tutorial Remote Database Connector (RDC) - Connect to any remote DB
20 21 22
Replies
423
Views
329K
Erel
  • Locked
  • Article
Android Tutorial SQL tutorial
7 8 9
Replies
161
Views
292K
luciano deri