Another complicated query but perhaps possible

[newbie2]

thank you sir for your crystal explanation.
I understand quite well your concept.
However in the case of the school to which I'm trying to help them with this application , the concept is different.
I have already talked to the school headmaster.
The school is paying the teacher from the incomes or receipts gathered from students' fees.
For example:
The school has gathered only 30 $ for James Cameron (10 +10 +10) for physics.
If they want to pay him right now, they calculate his payment based on this sum.
30 * 50 %*NumOfSessions.
The school is not going to pay him out of the incomes received from students for a specific subject and specific teacher.
I wish you would understand me.
thank you

[Zvoni]

thank you sir for your crystal explanation.
I understand quite well your concept.
However in the case of the school to which I'm trying to help them with this application , the concept is different.
I have already talked to the school headmaster.
The school is paying the teacher from the incomes or receipts gathered from students' fees.
For example:
The school has gathered only 30 $ for James Cameron (10 +10 +10) for physics.
If they want to pay him right now, they calculate his payment based on this sum.
30 * 50 %*NumOfSessions.
The school is not going to pay him out of the incomes received from students for a specific subject and specific teacher.
I wish you would understand me.
thank you

If the School only pays the teacher from the Student-Payments as they come in, then NumOfSessions has no influence on the PAYMENT at all, only for the Calculation of the Total

the School has gathered 30$ from 3 Payments from a student(10+10+10).
That PAYMENT doesn't know how many Sessions the student has booked!

the teacher gets 15 (5+5+5) --> 50% of the Payment

The NumOfSessions has no influence whatsoever on the PAYMENT the teacher receives, only on the Total!

Imagine a student books only one session for 100$, but pays in 5 installments (20$ each)
Student pays 20$ on monday, teacher receives 10$ on Thursday
Student pays 20$ on next monday, teacher receives 10$ on next Thursday.
and so on.....

No NumOfSessions whatsoever pertinent to PAYMENTS.
Only to calculation of totals

[newbie2]

If the School only pays the teacher from the Student-Payments as they come in, then NumOfSessions has no influence on the PAYMENT at all, only for the Calculation of the Total

you're completely right sir

[newbie2]

I think no escape of a second trasaction table.

[newbie2]

Let's take a single Subject (Physics), which the school says: "One session costs 100$"

In this case, Can a student negociate the price of a subject?

[Zvoni]

In this case, Can a student negociate the price of a subject?

Yes, since we left that field in student_class.
We‘d just have to add the NumOfSessions to calculate the total.
would have to look again at the query

[Zvoni]

I think no escape of a second trasaction table.

You are thinking the wrong way.
You said, this Program/Database is to help that School. Then you have to think like that School. You HAVE TO BE that School.
And the School looks at this (single) Transaction-table like if it's their Bank-Account:
"Hey, we have payments coming in from Students, and we have payments going out to teachers"

In this case, Can a student negociate the price of a subject?

If a Student negotiates an individual price, what's the teacher getting:
his ratio ("50%") of the "individual" Student's price, or the "original" Subject-Price?

[newbie2]

If a Student negotiates an individual price, what's the teacher getting:
his ratio ("50%") of the "individual" Student's price, or the "original" Subject-Price?

The answer is : his ratio ("50%") of the "individual" Student's price
As explained so far:
The school is not going to spend any money out of the students'fees.
It 's very complicated as described in the title of the post.

[Zvoni]

In tbl_student_class change Field Price to 40 for ID=1 (Adam Sandler pays 40$ for Physics instead of 100$)

New query for Student rates

SELECT ST.STName As Student, S.SName As Subject, COALESCE(SC.Price,S.SubjectPrice) As SubjectPrice, T.TName As TeacherName,
SC.NumOfSessions,
COALESCE(SC.Price,S.SubjectPrice)*SC.NumOfSessions As SubjectTotal,
COALESCE(TR.Payment, 0) As PaymentByStudentToSchool, 
COALESCE(SC.Price,S.SubjectPrice)*SC.NumOfSessions-SUM(COALESCE(TR.Payment, 0)) OVER(PARTITION BY SC.ID ORDER BY TR.PayDate) As RemainingSubjectPrice,
TR.PayDate
FROM tbl_student AS ST 
INNER JOIN tbl_student_class AS SC ON SC.Student_ID=ST.ID 
INNER JOIN tbl_teacher_subject AS TS ON TS.ID=SC.Class_ID 
INNER JOIN tbl_subject AS S ON S.ID=TS.Subject_ID
INNER JOIN tbl_teacher AS T ON T.ID=TS.Teacher_ID 
LEFT JOIN tbl_transaction AS TR ON TR.Student_Class_ID=SC.ID 
ORDER BY St.ID, SC.ID, TR.PayDate

Returns

Student	Subject	SubjectPrice	TeacherName	NumOfSessions	SubjectTotal	PaymentByStudentToSchool	RemainingSubjectPrice	PayDate
Adam Sandler	Physics	40.0	James Cameron	1	40.0	10.0	30.0	2023-01-10
Adam Sandler	Physics	40.0	James Cameron	1	40.0	10.0	20.0	2023-02-12
Adam Sandler	Physics	40.0	James Cameron	1	40.0	10.0	10.0	2023-02-28
Adam Sandler	Math	200.0	Steven Spielberg	2	400.0	20.0	380.0	2023-02-06
Adam Sandler	English	300.0	Stanley Kubrick	1	300.0	60.0	240.0	2023-01-24
Elijah Wood	Physics	100.0	Steven Spielberg	1	100.0	15.0	85.0	2023-02-25
Elijah Wood	English	300.0	James Cameron	2	600.0	0	600.0
Nicole Kidman	English	300.0	James Cameron	3	900.0	30.0	870.0	2023-02-09
Viggo Mortensen	Physics	100.0	James Cameron	2	200.0	0	200.0

New Query for teachers Payments

WITH 
    SC    AS (SELECT SC.Class_ID, SUM(COALESCE(SC.Price, S.SubjectPrice)*SC.NumOfSessions) As TotalPrice, 
                TS.TeacherPercentage, SUM(SC.NumOfSessions) As TotalSessions
                FROM tbl_student_class As SC
                INNER JOIN tbl_teacher_subject AS TS ON TS.ID=SC.Class_id
                INNER JOIN tbl_subject AS S ON S.ID=TS.Subject_ID
                GROUP BY SC.Class_ID, TS.TeacherPercentage)
SELECT 
S.SName As SubjectName, SC.TotalPrice, T.TName As TeacherName, SC.TeacherPercentage, 
SC.TotalPrice*TS.TeacherPercentage/100 As TeacherTotalPrice,
SC.TotalSessions, COALESCE(TR.Payment,0) AS Payment,
SC.TotalPrice*TS.TeacherPercentage/100-SUM(COALESCE(TR.Payment,0)) OVER(PARTITION BY SC.Class_ID ORDER BY TR.PayDate) As TotalTeacherPriceRemaining,
TR.Paydate 
FROM tbl_subject As S 
INNER JOIN tbl_teacher_subject As TS ON TS.Subject_ID=S.ID 
INNER JOIN tbl_teacher AS T ON T.ID=TS.Teacher_ID 
LEFT JOIN SC ON SC.Class_ID=TS.ID
LEFT JOIN tbl_transaction As TR ON TR.Teacher_Subject_ID=TS.ID

Physics with James Cameron consists of 2 Sessions for 100$ (Viggo Mortensen) and 1 Session for 40$ (Adam Sandler)
sum = 240$ --> Teacher gets 50% of that

Returns

SubjectName	TotalPrice	TeacherName	TeacherPercentage	TeacherTotalPrice	TotalSessions	Payment	TotalTeacherPriceRemaining	PayDate
Physics	240.0	James Cameron	50.0	120.0	3	30.0	90.0	2023-03-05
Physics	240.0	James Cameron	50.0	120.0	3	25.0	65.0	2023-03-15
English	1500.0	James Cameron	25.0	375.0	5	30.0	345.0	2023-02-15
Math	400.0	Steven Spielberg	75.0	300.0	2	0	300.0
English	300.0	Stanley Kubrick	50.0	150.0	1	0	150.0
Physics	100.0	Steven Spielberg	25.0	25.0	1	0	25.0

[newbie2]

Zvoni
Thank you very very much
I'm so grateful to you
I'll keep the thread open for some days
If I meet a problem, I'll come back.

[newbie2]

Hello Zvoni
I have created a new table (Payment_tbl)
In this table I mark all the transactions that are complete.
If the sum(payments) in the (transaction_tbl) = the price in (the tbl_student_class),
I set "Paid" = 1 for the corresponding transaction in (the payment_tbl).
I tried this query but did not succeed.

Update Payment_tbl Set Due = 1 " & _
" FROM (SELECT sum(Payment) as Payment,Price FROM tbl_transaction  " & _
" LEFT JOIN tbl_student_class ON tbl_transaction.Student_Class_ID = tbl_student_class.ID  " & _
" WHERE   Payment = Price)"

Thank you

[newbie2]

I was able to solve this ussue by a workaroud.
Instead of creating a new table, I added a column "Paid" in "tbl_student_class" table.
then I used this code:

StrSql = "Select sum(Payment) as Payment,tbl_student_class.Price FROM tbl_transaction  " & _
" LEFT JOIN tbl_student_class ON tbl_transaction.Student_Class_ID = tbl_student_class.ID " & _
" where Student_Class_ID = " & txt_ID.Text
Set Rs = Cnn.OpenRecordset(StrSql)
If Rs!Payment = Rs!Price Then
Cnn.Execute "Update tbl_student_class Set paid  = 1 WHERE ID = " & txt_ID.Text
End If

It worked

[Zvoni]

How to update everything in one go.
Even respects if Student has no "individual" Price but the regular SubjectPrice

WITH    
    CTE AS (SELECT SC.ID, COALESCE(SC.Price,PR.SubjectPrice) As StudentPrice, COALESCE(TR.Payment, 0) As Payment,
            (C ASE WHEN COALESCE(SC.Price,PR.SubjectPrice)=COALESCE(TR.Payment, 0) THEN 1 ELSE 0 END) As CalcPaid
            FROM tbl_student_class AS SC 
            INNER JOIN (SELECT ID, SubjectPrice FROM tbl_subject) As PR ON PR.ID=SC.Class_ID
            LEFT JOIN (SELECT Student_Class_ID, SUM(Payment) As Payment FROM tbl_transaction GROUP BY Student_Class_ID) As TR 
            ON TR.Student_Class_ID=SC.ID) 

UPDATE tbl_student_class As TSC
SET Paid=CTE.CalcPaid 
FROM CTE 
WHERE CTE.ID=TSC.ID

BEWARE: Due to Forum-Issues i had to insert a SPACE in the word C ASE above

[Zvoni]

Whooops!

NumOFSessions was missing above

 	WITH    
    CTE AS (SELECT SC.ID, SC.NumOfSessions*COALESCE(SC.Price,PR.SubjectPrice) As StudentPrice, COALESCE(TR.Payment, 0) As Payment,
            (C ASE WHEN SC.NumOfSessions*COALESCE(SC.Price,PR.SubjectPrice)=COALESCE(TR.Payment, 0) THEN 1 ELSE 0 END) As CalcPaid
            FROM tbl_student_class AS SC 
            INNER JOIN (SELECT ID, SubjectPrice FROM tbl_subject) As PR ON PR.ID=SC.Class_ID
            LEFT JOIN (SELECT Student_Class_ID, SUM(Payment) As Payment FROM tbl_transaction GROUP BY Student_Class_ID) As TR 
            ON TR.Student_Class_ID=SC.ID) 

UPDATE tbl_student_class As TSC
SET Paid=CTE.CalcPaid 
FROM CTE 
WHERE CTE.ID=TSC.ID

And again the blank in C ASE because of Forum-Problems

[newbie2]

Zvoni
Thank you very much for the very sophisticated code .
It worked like a charm.
thank you again

[newbie2]

You are so generous
Million thanks sir

[newbie2]

Zvoni Excuse me
I'm getting crasy to make the last code working.
I spent a whole night trying to understand the code.

StrSql = "WITH " & _
   " CTE AS (SELECT SC.ID, SC.NumOfSessions*COALESCE(SC.Price,PR.SubjectPrice) As StudentPrice, COALESCE(TR.Payment, 0) As Payment, " & _
          "  (CASE WHEN SC.NumOfSessions*COALESCE(SC.Price,PR.SubjectPrice)=COALESCE(TR.Payment, 0) THEN 1 ELSE 0 END) As CalcPaid " & _
           " FROM tbl_student_class AS SC " & _
           " INNER JOIN (SELECT ID, SubjectPrice FROM tbl_subject) As PR ON PR.ID=SC.Class_ID " & _
           " LEFT JOIN (SELECT Student_Class_ID, SUM(Payment) As Payment FROM tbl_transaction GROUP BY Student_Class_ID) As TR " & _
           " ON TR.Student_Class_ID=SC.ID) " & _
" UPDATE tbl_student_class As TSC " & _
" Set Paid = CTE.CalcPaid " & _
" From CTE " & _
" Where CTE.ID = TSC.ID "
 Set Rs = Cnn.OpenRecordset(StrSql)

Everything seems OK except the last line.
Please what is CTE.ID?
Is it tbl_student_class.Class_ID?
And what is TSC.ID ?
How can I check the price and the sum of payments by means of "Debug.print"?
Yesterday everything was working but today I could not get the code work properly.
I made the price equal to the sum payments then I find some records are updated (Paid = 1) while others are not (Paid = 0).
thank you

[Zvoni]

Right.

CTE = Common Table Expression
Think of it like a temporary VIEW or table the query creates on the fly while executing that query

As for debugging:
Split out the inner SELECT of the CTE

Kind of (note again the blank in C ASE )
Aircode

Dim sCTE As String
Dim sLJ As String  'For the LEFT JOIN
dim sIJ As String 'For the INNER JOIN
Dim SQL As String

SQL="WITH CTE AS("

sCTE="SELECT SC.ID, SC.NumOfSessions*COALESCE(SC.Price,PR.SubjectPrice) As StudentPrice, COALESCE(TR.Payment, 0) As Payment, "
sCTE=sCTE & "(C ASE WHEN SC.NumOfSessions*COALESCE(SC.Price,PR.SubjectPrice)=COALESCE(TR.Payment, 0) THEN 1 ELSE 0 END) As CalcPaid "

sIJ="SELECT ID, SubjectPrice FROM tbl_subject"

sCTE=sCTE & "FROM tbl_student_class AS SC INNER JOIN (" & sIJ & ") As PR ON PR.ID=SC.Class_ID "

sLJ="SELECT Student_Class_ID, SUM(Payment) As Payment FROM tbl_transaction GROUP BY Student_Class_ID"

sCTE=sCTE & "LEFT JOIN (" & sLJ & ") As TR "
sCTE=sCTE & "ON TR.Student_Class_ID=SC.ID) "
SQL=SQL & sCTE & "UPDATE tbl_student_class As TSC SET Paid=CTE.CalcPaid FROM CTE WHERE CTE.ID=TSC.ID"

Set Rs=Cnn.OpenRecordSet(sIJ)  'We're only executing the INNER JOIN-Part!!!
Debug.Print Rs("ID")
Debug.Print Rs("SubjectPrice")

'If OK, then execute the LEFT JOIN
Set Rs=Cnn.OpenRecordSet(sLJ)  'We're only executing the LEFT JOIN-Part!!!
Debug.Print Rs("Student_Class_ID")
Debug.Print Rs("Payment")

'If OK, then execute the CTE
Set Rs=Cnn.OpenRecordSet(sCTE)  'We're only executing the CTE!!!
Debug.Print Rs("ID")
Debug.Print Rs("StudentPrice")  'This is NumOfSessions multiplied with either individual StudentPrice or SubjectPrice
Debug.Print Rs("Payment")
Debug.Print Rs("CalcPaid")

'If everything OK, execute everything
Set Rs=Cnn.OpenRecordSet(SQL)

As for why it returned 0 instead of 1: Could be because those are floating-point-values. Might have to introduce a rounding
Or you have a BLANK in Student-Price instead of a real NULL. But there is a solution, too, to catch those

[newbie2]

Zvoni
I'm getting error in misplacing the bracket ")"
Cannot compile Select statement ")" : syntax error

Dim sCTE As String
Dim sLJ As String  'For the LEFT JOIN
Dim sIJ As String 'For the INNER JOIN
Dim SQL As String
SQL = "WITH CTE AS("
sCTE = "SELECT SC.ID, SC.NumOfSessions*COALESCE(SC.Price,PR.SubjectPrice) As StudentPrice, COALESCE(TR.Payment, 0) As Payment, "
sCTE = sCTE & "(CASE WHEN SC.NumOfSessions*COALESCE(SC.Price,PR.SubjectPrice)=COALESCE(TR.Payment, 0) THEN 1 ELSE 0 END) As CalcPaid "
sIJ = "SELECT ID, SubjectPrice FROM tbl_subject"
sCTE = sCTE & "FROM tbl_student_class AS SC INNER JOIN (" & sIJ & ") As PR ON PR.ID=SC.Class_ID "
sLJ = "SELECT Student_Class_ID, SUM(Payment) As Payment FROM tbl_transaction GROUP BY Student_Class_ID"
sCTE = sCTE & "LEFT JOIN (" & sLJ & ") As TR "
sCTE = sCTE & "ON TR.Student_Class_ID=SC.ID ) "
SQL = SQL & sCTE & "UPDATE tbl_student_class As TSC SET Paid=CTE.CalcPaid FROM CTE WHERE CTE.ID=TSC.ID"
Set Rs = Cnn.OpenRecordset(sIJ) 'We're only executing the INNER JOIN-Part!!!
Debug.Print Rs("ID")
Debug.Print Rs("SubjectPrice")
'If OK, then execute the LEFT JOIN
Set Rs = Cnn.OpenRecordset(sLJ) 'We're only executing the LEFT JOIN-Part!!!
Debug.Print Rs("Student_Class_ID")
Debug.Print Rs("Payment")
'If OK, then execute the CTE
Set Rs = Cnn.OpenRecordset(sCTE) 'We're only executing the CTE!!!
Debug.Print Rs("ID")
Debug.Print Rs("StudentPrice")  'This is NumOfSessions multiplied with either individual StudentPrice or SubjectPrice
Debug.Print Rs("Payment")
Debug.Print Rs("CalcPaid")
'If everything OK, execute everything
Set Rs = Cnn.OpenRecordset(SQL)

[Zvoni]

Found it

Dim sCTE As String
Dim sLJ As String  'For the LEFT JOIN
Dim sIJ As String 'For the INNER JOIN
Dim SQL As String

SQL = "WITH CTE AS("

sCTE = "SELECT SC.ID, SC.NumOfSessions*COALESCE(SC.Price,PR.SubjectPrice) As StudentPrice, COALESCE(TR.Payment, 0) As Payment, "
sCTE = sCTE & "(C ASE WHEN SC.NumOfSessions*COALESCE(SC.Price,PR.SubjectPrice)=COALESCE(TR.Payment, 0) THEN 1 ELSE 0 END) As CalcPaid "

sIJ = "SELECT ID, SubjectPrice FROM tbl_subject"

sCTE = sCTE & "FROM tbl_student_class AS SC INNER JOIN (" & sIJ & ") As PR ON PR.ID=SC.Class_ID "

sLJ = "SELECT Student_Class_ID, SUM(Payment) As Payment FROM tbl_transaction GROUP BY Student_Class_ID"

sCTE = sCTE & "LEFT JOIN (" & sLJ & ") As TR "
sCTE = sCTE & "ON TR.Student_Class_ID=SC.ID" 'HERE THE CLOSING BRACKET REMOVED

'Here the Closing Bracket inserted
SQL = SQL & sCTE & ") UPDATE tbl_student_class As TSC SET Paid=CTE.CalcPaid FROM CTE WHERE CTE.ID=TSC.ID"

Note again the BLANK in C ASE above

[newbie2]

I'm sorry sir I'm still having trouble.
I can't figure out where the problem is.

SQL = SQL & sCTE & ") UPDATE tbl_student_class As TSC SET Paid=CTE.CalcPaid FROM CTE WHERE CTE.ID=TSC.ID"
Set Rs = Cnn.OpenRecordset(sLJ)
Debug.Print Rs("Student_Class_ID")
Debug.Print Rs("Payment")
Set Rs = Cnn.OpenRecordset(sCTE)
Debug.Print Rs("ID")
Debug.Print Rs("StudentPrice")
Debug.Print Rs("CalcPaid")
Set Rs = Cnn.OpenRecordset(SQL)

This is the output:
100
1
100
0

[Zvoni]

Which (Sub-) Query, and which Fields?
What are the Values for the other Fields?

[newbie2]

   Dim sSQL As String
 DBFile = App.path & "\Example.db"
 Set Cnn = New RC6.cConnection

    If Dir(DBFile, vbNormal) = vbNullString Then
        If Cnn.CreateNewDB(DBFile) = False Then
            MsgBox "Failed to create new sqlite database !" & _
                        vbCrLf & vbCrLf & Cnn.LastDBError

    End If
    End If
'

 Set Cnn = New_c.Connection(DBFile)
With Cnn.NewFieldDefs
.Add "ID   Integer Primary Key"
 .Add "STName   TEXT "
 .Add "Sfees   Double"
End With
Cnn.CreateTable "tbl_student"
'
With Cnn.NewFieldDefs
.Add "ID    INTEGER Primary Key"
 .Add "SName    TEXT"
.Add "SubjectPrice    REAL DEFAULT NULL"
End With
Cnn.CreateTable "tbl_subject"
'
 With Cnn.NewFieldDefs
.Add "ID    INTEGER Primary Key"
 .Add "TName    TEXT NOT NULL"
 End With
Cnn.CreateTable "tbl_teacher"
''
With Cnn.NewFieldDefs
  .Add "ID    INTEGER Primary Key "
  .Add "Teacher_ID    INTEGER  "
  .Add "Subject_ID    INTEGER"
  .Add "TeacherPercentage    REAL NOT NULL"
  .Add "ClassPrice    REAL DEFAULT NULL," & _
   " FOREIGN KEY(Subject_ID) REFERENCES tbl_subject(ID) ON UPDATE CASCADE ON DELETE CASCADE ," & _
   " FOREIGN KEY(Teacher_ID) REFERENCES tbl_teacher(ID) ON UPDATE CASCADE ON DELETE RESTRICT"
End With
Cnn.CreateTable "tbl_teacher_subject"
'
With Cnn.NewFieldDefs
  .Add "ID    INTEGER PRIMARY KEY"
  .Add "Student_ID    INTEGER  "
  .Add "Class_ID    INTEGER"
  .Add "Price    REAL DEFAULT NULL "
  .Add "Paid    INTEGER"
  .Add "NumOfSessions INTEGER DEFAULT 1," & _
  " FOREIGN KEY(Class_ID) REFERENCES tbl_teacher_subject(ID) ON UPDATE CASCADE ON DELETE CASCADE ," & _
  "  FOREIGN KEY(Student_ID) REFERENCES tbl_student(ID) ON UPDATE CASCADE ON DELETE CASCADE"
End With
Cnn.CreateTable "tbl_student_class"

With Cnn.NewFieldDefs
   .Add "ID   INTEGER PRIMARY KEY "
    .Add "Payment    REAL "
    .Add "PayDate    TEXT "
    .Add "Student_Class_ID   INTEGER DEFAULT NULL "
    .Add "Teacher_Subject_ID    INTEGER DEFAULT NULL ," & _
    " FOREIGN KEY(Student_Class_ID) REFERENCES tbl_student_class(ID) ON UPDATE CASCADE ON DELETE RESTRICT ," & _
    " FOREIGN KEY(Teacher_Subject_ID) REFERENCES tbl_teacher_subject(ID) ON UPDATE CASCADE ON DELETE RESTRICT ," & _
    " CONSTRAINT CheckForeignKeyXOR CHECK((Student_Class_ID IS NULL AND teacher_Subject_ID IS NOT NULL) OR (Student_Class_ID IS NOT NULL AND Teacher_Subject_ID IS NULL))"
End With
Cnn.CreateTable "tbl_transaction"

Cnn.Execute "INSERT INTO tbl_student (ID, STName) VALUES ('1', 'Adam Sandler')"
Cnn.Execute "INSERT INTO tbl_student (ID, STName) VALUES ('2', 'Elijah Wood')"
Cnn.Execute "INSERT INTO tbl_student (ID, STName) VALUES ('3', 'Nicole Kidman')"

Cnn.Execute "INSERT INTO tbl_teacher (ID, TName) VALUES ('1', 'James Cameron')"
Cnn.Execute "INSERT INTO tbl_teacher (ID, TName) VALUES ('2', 'Steven Spielberg')"
Cnn.Execute "INSERT INTO tbl_teacher (ID, TName) VALUES ('3', 'Stanley Kubrick')"
'
Cnn.Execute "INSERT INTO tbl_subject (ID, SName, SubjectPrice) VALUES ('1', 'Physics', '100.0')"
Cnn.Execute "INSERT INTO tbl_subject (ID, SName, SubjectPrice) VALUES ('2', 'Math', '200.0')"
Cnn.Execute "INSERT INTO tbl_subject (ID, SName, SubjectPrice) VALUES ('3', 'English', '300.0')"

Cnn.Execute "INSERT INTO tbl_teacher_subject (ID, Teacher_ID, Subject_ID, TeacherPercentage) VALUES ('1', '1', '1', '50.0')"
Cnn.Execute "INSERT INTO tbl_teacher_subject (ID, Teacher_ID, Subject_ID, TeacherPercentage) VALUES ('2', '1', '3', '25.0')"
Cnn.Execute "INSERT INTO tbl_teacher_subject (ID, Teacher_ID, Subject_ID, TeacherPercentage) VALUES ('3', '2', '2', '75.0')"
Cnn.Execute "INSERT INTO tbl_teacher_subject (ID, Teacher_ID, Subject_ID, TeacherPercentage) VALUES ('4', '3', '3', '50.0')"
Cnn.Execute "INSERT INTO tbl_teacher_subject (ID, Teacher_ID, Subject_ID, TeacherPercentage) VALUES ('5', '2', '1', '25.0')"

Cnn.Execute "INSERT INTO tbl_student_class (ID, Student_ID, Class_ID, Price,NumOfSessions) VALUES ('1', '1', '1', 10,1)"
Cnn.Execute "INSERT INTO tbl_student_class (ID, Student_ID, Class_ID, Price,NumOfSessions) VALUES ('2', '1', '3', 20,2)"
Cnn.Execute "INSERT INTO tbl_student_class (ID, Student_ID, Class_ID, Price,NumOfSessions) VALUES ('3', '2', '5', 30,1)"
Cnn.Execute "INSERT INTO tbl_student_class (ID, Student_ID, Class_ID, Price,NumOfSessions) VALUES ('4', '3', '2', 40,3)"
Cnn.Execute "INSERT INTO tbl_student_class (ID, Student_ID, Class_ID, Price,NumOfSessions) VALUES ('5', '1', '4', 50,1)"
Cnn.Execute "INSERT INTO tbl_student_class (ID, Student_ID, Class_ID, Price,NumOfSessions) VALUES ('6', '2', '2', 60,2)"


Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('1', '50.0', '2023-01-10', '1', NULL);"
Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('2', '70.0', '2023-02-06', '2', NULL);"
Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('3', '80.0', '2023-02-09', '4', NULL);"
Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('4', '5.0', '2023-02-12', '1', NULL);"
Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('5', '60.0', '2023-02-25', '3', NULL);"
Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('6', '100.0', '2023-02-28', '1', NULL);"
Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('7', '80.0', '2023-01-24', '5', NULL);"
Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('8', '30.0', '2023-03-05', NULL, '1');"
Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('9', '30.0', '2023-02-15', NULL, '2');"
Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('10', '25.0', '2023-03-15', NULL, '1');"

Dim sCTE As String
Dim sLJ As String
Dim sIJ As String
Dim SQL As String

SQL = "WITH CTE AS("

sCTE = "SELECT SC.ID, SC.NumOfSessions*COALESCE(SC.Price,PR.SubjectPrice) As StudentPrice, COALESCE(TR.Payment, 0) As Payment, "
sCTE = sCTE & "(CASE WHEN SC.NumOfSessions*COALESCE(SC.Price,PR.SubjectPrice)=COALESCE(TR.Payment, 0) THEN 1 ELSE 0 END) As CalcPaid "

sIJ = "SELECT ID, SubjectPrice FROM tbl_subject"

sCTE = sCTE & "FROM tbl_student_class AS SC INNER JOIN (" & sIJ & ") As PR ON PR.ID=SC.Class_ID "

sLJ = "SELECT Student_Class_ID, SUM(Payment) As Payment FROM tbl_transaction GROUP BY Student_Class_ID"

sCTE = sCTE & "LEFT JOIN (" & sLJ & ") As TR "
sCTE = sCTE & "ON TR.Student_Class_ID=SC.ID"

SQL = SQL & sCTE & ") UPDATE tbl_student_class As TSC SET Paid=CTE.CalcPaid FROM CTE WHERE CTE.ID=TSC.ID"
Set Rs = Cnn.OpenRecordset(sLJ)
Debug.Print Rs("Student_Class_ID")
Debug.Print Rs("Payment")
Set Rs = Cnn.OpenRecordset(sCTE)
Debug.Print Rs("ID")
Debug.Print Rs("StudentPrice")
Debug.Print Rs("CalcPaid")
Set Rs = Cnn.OpenRecordset(SQL)

[Zvoni]

And which one comes out "wrong"?
That's the sample setup of the database i posted earlier

[newbie2]

Dim sSQL As String
 DBFile = App.path & "\Example2.db"
 Set Cnn = New RC6.cConnection

    If Dir(DBFile, vbNormal) = vbNullString Then
        If Cnn.CreateNewDB(DBFile) = False Then
            MsgBox "Failed to create new sqlite database !" & _
                        vbCrLf & vbCrLf & Cnn.LastDBError

    End If
    End If
'

 Set Cnn = New_c.Connection(DBFile)
With Cnn.NewFieldDefs
.Add "ID   Integer Primary Key"
 .Add "STName   TEXT "
 .Add "Sfees   Double"
End With
Cnn.CreateTable "tbl_student"
'
With Cnn.NewFieldDefs
.Add "ID    INTEGER Primary Key"
 .Add "SName    TEXT"
.Add "SubjectPrice    REAL DEFAULT NULL"
End With
Cnn.CreateTable "tbl_subject"
'
 With Cnn.NewFieldDefs
.Add "ID    INTEGER Primary Key"
 .Add "TName    TEXT NOT NULL"
 End With
Cnn.CreateTable "tbl_teacher"
''
With Cnn.NewFieldDefs
  .Add "ID    INTEGER Primary Key "
  .Add "Teacher_ID    INTEGER  "
  .Add "Subject_ID    INTEGER"
  .Add "TeacherPercentage    REAL NOT NULL"
  .Add "ClassPrice    REAL DEFAULT NULL," & _
   " FOREIGN KEY(Subject_ID) REFERENCES tbl_subject(ID) ON UPDATE CASCADE ON DELETE CASCADE ," & _
   " FOREIGN KEY(Teacher_ID) REFERENCES tbl_teacher(ID) ON UPDATE CASCADE ON DELETE RESTRICT"
End With
Cnn.CreateTable "tbl_teacher_subject"
'
With Cnn.NewFieldDefs
  .Add "ID    INTEGER PRIMARY KEY"
  .Add "Student_ID    INTEGER  "
  .Add "Class_ID    INTEGER"
  .Add "Price    REAL DEFAULT NULL "
  .Add "Paid    INTEGER"
  .Add "NumOfSessions INTEGER DEFAULT 1," & _
  " FOREIGN KEY(Class_ID) REFERENCES tbl_teacher_subject(ID) ON UPDATE CASCADE ON DELETE CASCADE ," & _
  "  FOREIGN KEY(Student_ID) REFERENCES tbl_student(ID) ON UPDATE CASCADE ON DELETE CASCADE"
End With
Cnn.CreateTable "tbl_student_class"

With Cnn.NewFieldDefs
   .Add "ID   INTEGER PRIMARY KEY "
    .Add "Payment    REAL "
    .Add "PayDate    TEXT "
    .Add "Student_Class_ID   INTEGER DEFAULT NULL "
    .Add "Teacher_Subject_ID    INTEGER DEFAULT NULL ," & _
    " FOREIGN KEY(Student_Class_ID) REFERENCES tbl_student_class(ID) ON UPDATE CASCADE ON DELETE RESTRICT ," & _
    " FOREIGN KEY(Teacher_Subject_ID) REFERENCES tbl_teacher_subject(ID) ON UPDATE CASCADE ON DELETE RESTRICT ," & _
    " CONSTRAINT CheckForeignKeyXOR CHECK((Student_Class_ID IS NULL AND teacher_Subject_ID IS NOT NULL) OR (Student_Class_ID IS NOT NULL AND Teacher_Subject_ID IS NULL))"
End With
Cnn.CreateTable "tbl_transaction"

Cnn.Execute "INSERT INTO tbl_student (ID, STName) VALUES ('1', 'Adam Sandler')"
Cnn.Execute "INSERT INTO tbl_student (ID, STName) VALUES ('2', 'Elijah Wood')"
Cnn.Execute "INSERT INTO tbl_student (ID, STName) VALUES ('3', 'Nicole Kidman')"

Cnn.Execute "INSERT INTO tbl_teacher (ID, TName) VALUES ('1', 'James Cameron')"
Cnn.Execute "INSERT INTO tbl_teacher (ID, TName) VALUES ('2', 'Steven Spielberg')"
Cnn.Execute "INSERT INTO tbl_teacher (ID, TName) VALUES ('3', 'Stanley Kubrick')"
'
Cnn.Execute "INSERT INTO tbl_subject (ID, SName, SubjectPrice) VALUES ('1', 'Physics', '100.0')"
Cnn.Execute "INSERT INTO tbl_subject (ID, SName, SubjectPrice) VALUES ('2', 'Math', '200.0')"
Cnn.Execute "INSERT INTO tbl_subject (ID, SName, SubjectPrice) VALUES ('3', 'English', '300.0')"

Cnn.Execute "INSERT INTO tbl_teacher_subject (ID, Teacher_ID, Subject_ID, TeacherPercentage) VALUES ('1', '1', '1', '50.0')"
Cnn.Execute "INSERT INTO tbl_teacher_subject (ID, Teacher_ID, Subject_ID, TeacherPercentage) VALUES ('2', '1', '3', '25.0')"
Cnn.Execute "INSERT INTO tbl_teacher_subject (ID, Teacher_ID, Subject_ID, TeacherPercentage) VALUES ('3', '2', '2', '75.0')"
Cnn.Execute "INSERT INTO tbl_teacher_subject (ID, Teacher_ID, Subject_ID, TeacherPercentage) VALUES ('4', '3', '3', '50.0')"
Cnn.Execute "INSERT INTO tbl_teacher_subject (ID, Teacher_ID, Subject_ID, TeacherPercentage) VALUES ('5', '2', '1', '25.0')"

Cnn.Execute "INSERT INTO tbl_student_class (ID, Student_ID, Class_ID, Price,Paid, NumOfSessions) VALUES ('1', '1', '1', 85,NULL,1)"
Cnn.Execute "INSERT INTO tbl_student_class (ID, Student_ID, Class_ID, Price,Paid,NumOfSessions) VALUES ('2', '1', '3', 70,NULL,2)"
Cnn.Execute "INSERT INTO tbl_student_class (ID, Student_ID, Class_ID, Price,Paid,NumOfSessions) VALUES ('3', '2', '5', 80,NULL,1)"
Cnn.Execute "INSERT INTO tbl_student_class (ID, Student_ID, Class_ID, Price,Paid,NumOfSessions) VALUES ('4', '3', '2', 40,NULL,3)"
Cnn.Execute "INSERT INTO tbl_student_class (ID, Student_ID, Class_ID, Price,Paid,NumOfSessions) VALUES ('5', '1', '4', 50,NULL,1)"
Cnn.Execute "INSERT INTO tbl_student_class (ID, Student_ID, Class_ID, Price,Paid,NumOfSessions) VALUES ('6', '2', '2', 60,NULL,2)"

Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('1', '50.0', '2023-01-10', '1', NULL);"
Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('2', '70.0', '2023-02-06', '2', NULL);"
Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('3', '80.0', '2023-02-09', '4', NULL);"
Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('4', '50.0', '2023-02-12', '1', NULL);"
Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('5', '60.0', '2023-02-25', '3', NULL);"
Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('6', '100.0', '2023-02-28', '1', NULL);"
Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('7', '80.0', '2023-01-24', '5', NULL);"
Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('8', '30.0', '2023-03-05', NULL, '1');"
Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('9', '30.0', '2023-02-15', NULL, '2');"
Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('10', '25.0', '2023-03-15', NULL, '1');"

Dim sCTE As String
Dim sLJ As String
Dim sIJ As String
Dim SQL As String

SQL = "WITH CTE AS("

sCTE = "SELECT SC.ID, SC.NumOfSessions*COALESCE(SC.Price,PR.SubjectPrice) As StudentPrice, COALESCE(TR.Payment, 0) As Payment, "
sCTE = sCTE & "(CASE WHEN SC.NumOfSessions*COALESCE(SC.Price,PR.SubjectPrice)=COALESCE(TR.Payment, 0) THEN 1 ELSE 0 END) As CalcPaid "

sIJ = "SELECT ID, SubjectPrice FROM tbl_subject"

sCTE = sCTE & "FROM tbl_student_class AS SC INNER JOIN (" & sIJ & ") As PR ON PR.ID=SC.Class_ID "

sLJ = "SELECT Student_Class_ID, SUM(Payment) As Payment FROM tbl_transaction GROUP BY Student_Class_ID"

sCTE = sCTE & "LEFT JOIN (" & sLJ & ") As TR "
sCTE = sCTE & "ON TR.Student_Class_ID=SC.ID"

SQL = SQL & sCTE & ") UPDATE tbl_student_class As TSC SET Paid=CTE.CalcPaid FROM CTE WHERE CTE.ID=TSC.ID"
Set Rs = Cnn.OpenRecordset(sLJ)
Debug.Print Rs("Student_Class_ID")
Debug.Print Rs("Payment")
Set Rs = Cnn.OpenRecordset(sCTE)
Debug.Print Rs("ID")
Debug.Print Rs("StudentPrice")
Debug.Print Rs("CalcPaid")
Set Rs = Cnn.OpenRecordset(SQL)

[Zvoni]

I just took your latest values.

Query to show students returns this:

Student	Subject	SubjectPrice	TeacherName	NumOfSessions	SubjectTotal	PaymentByStudentToSchool	RemainingSubjectPrice	PayDate
Adam Sandler	Physics	10.0	James Cameron	1	10.0	50.0	-40.0	2023-01-10
Adam Sandler	Physics	10.0	James Cameron	1	10.0	5.0	-45.0	2023-02-12
Adam Sandler	Physics	10.0	James Cameron	1	10.0	100.0	-145.0	2023-02-28
Adam Sandler	Math	20.0	Steven Spielberg	2	40.0	70.0	-30.0	2023-02-06
Adam Sandler	English	50.0	Stanley Kubrick	1	50.0	80.0	-30.0	2023-01-24
Elijah Wood	Physics	30.0	Steven Spielberg	1	30.0	60.0	-30.0	2023-02-25
Elijah Wood	English	60.0	James Cameron	2	120.0	0	120.0
Nicole Kidman	English	40.0	James Cameron	3	120.0	80.0	40.0	2023-02-09

Where in blazes is the "100" coming from?
And this is the result of just the CTE from above

ID	StudentPrice	Payment	CalcPaid
1	10.0	155.0	0
2	40.0	70.0	0
4	120.0	80.0	0
6	120.0	0	0

Where did you expect Paid=1

[newbie2]

The the "100" is coming from the table subject
I sent the wrong code.
I fact in my original code, the student price is set to null
I think I need some time to concentrate carefully.
Perhaps due to my tiredness I'm doing such mistakes.
I'm sorry sir for disturbing you.

[Zvoni]

btw: It might be worth considering changing the equal-comparison within the C ASE-Block from "=" to "<="
Meaning: If the total payment exceeds the total SubjectPrice (or StudentPrice) to set the Record to Paid=1
(for whatever reason the total Payment might be higher than the total price)

[newbie2]

Hello Zvoni
I need to retrieve the teachers who have completely received their pays.
For that I used this code but it doesn't seem to work properly.

WITH 
    SC    AS (SELECT SC.Class_ID, SUM(COALESCE(SC.Price, S.SubjectPrice)*SC.NumOfSessions) As TotalPrice, 
                TS.TeacherPercentage, SUM(SC.NumOfSessions) As TotalSessions
                FROM tbl_student_class As SC
                INNER JOIN tbl_teacher_subject AS TS ON TS.ID=SC.Class_id
                INNER JOIN tbl_subject AS S ON S.ID=TS.Subject_ID
                GROUP BY SC.Class_ID, TS.TeacherPercentage)
SELECT 
S.SName As SubjectName, SC.TotalPrice, T.TName As TeacherName, SC.TeacherPercentage, 
SC.TotalPrice*TS.TeacherPercentage/100 As TeacherTotalPrice,
SC.TotalSessions, COALESCE(TR.Payment,0) AS Payment,
SC.TotalPrice*TS.TeacherPercentage/100-SUM(COALESCE(TR.Payment,0)) OVER(PARTITION BY SC.Class_ID ORDER BY TR.PayDate) As TotalTeacherPriceRemaining,
TR.Paydate 
FROM tbl_subject As S 
INNER JOIN tbl_teacher_subject As TS ON TS.Subject_ID=S.ID 
INNER JOIN tbl_teacher AS T ON T.ID=TS.Teacher_ID 
LEFT JOIN SC ON SC.Class_ID=TS.ID
LEFT JOIN tbl_transaction As TR ON TR.Teacher_Subject_ID=TS.ID Where  TeacherTotalPrice= Payment"

Then I used this code but it is sending error:
(Misuse og aggeegate sum)

WITH 
    SC    AS (SELECT SC.Class_ID, SUM(COALESCE(SC.Price, S.SubjectPrice)*SC.NumOfSessions) As TotalPrice, 
                TS.TeacherPercentage, SUM(SC.NumOfSessions) As TotalSessions
                FROM tbl_student_class As SC
                INNER JOIN tbl_teacher_subject AS TS ON TS.ID=SC.Class_id
                INNER JOIN tbl_subject AS S ON S.ID=TS.Subject_ID
                GROUP BY SC.Class_ID, TS.TeacherPercentage)
SELECT 
S.SName As SubjectName, SC.TotalPrice, T.TName As TeacherName, SC.TeacherPercentage, 
SC.TotalPrice*TS.TeacherPercentage/100 As TeacherTotalPrice,
SC.TotalSessions, SUM(COALESCE(TR.Payment,0)) AS Payment,
TR.Paydate 
FROM tbl_subject As S 
INNER JOIN tbl_teacher_subject As TS ON TS.Subject_ID=S.ID 
INNER JOIN tbl_teacher AS T ON T.ID=TS.Teacher_ID 
LEFT JOIN SC ON SC.Class_ID=TS.ID
LEFT JOIN tbl_transaction As TR ON TR.Teacher_Subject_ID=TS.ID Where  TeacherTotalPrice= Payment"

Am I doing something wrong?
thank you

[Zvoni]

Will be on monday

[newbie2]

Will be on monday

Thank you very much sir

[newbie2]

I think I got it but I need your confirmation

WITH 
    SC    AS (SELECT SC.Class_ID, SUM(COALESCE(SC.Price, S.SubjectPrice)*SC.NumOfSessions) As TotalPrice, 
                TS.TeacherPercentage, SUM(SC.NumOfSessions) As TotalSessions
                FROM tbl_student_class As SC
                INNER JOIN tbl_teacher_subject AS TS ON TS.ID=SC.Class_id
                INNER JOIN tbl_subject AS S ON S.ID=TS.Subject_ID
                GROUP BY SC.Class_ID, TS.TeacherPercentage)
SELECT 
S.SName As SubjectName, SC.TotalPrice, T.TName As TeacherName, SC.TeacherPercentage, 
SC.TotalPrice*TS.TeacherPercentage/100 As TeacherTotalPrice,
SC.TotalSessions, SUM(COALESCE(TR.Payment,0)) AS Payment,
TR.Paydate 
FROM tbl_subject As S 
INNER JOIN tbl_teacher_subject As TS ON TS.Subject_ID=S.ID 
INNER JOIN tbl_teacher AS T ON T.ID=TS.Teacher_ID 
LEFT JOIN SC ON SC.Class_ID=TS.ID
LEFT JOIN tbl_transaction As TR ON TR.Teacher_Subject_ID=TS.ID
GROUP BY SC.Class_ID, SC.TeacherPercentage
Having  TeacherTotalPrice= Payment

[newbie2]

Hello Zvoni
I've just noticed an issue.
I think that the "tbl_teacher_subject" should have the "ID" field AUTOINCREMENT .
Let's imagine this scenario:
Teacher "X" has been attributed (Class_ID 2 and 3) in tbl_student_class"
Then Teacher "X" is deleted in "tbl_teacher_subject" and a teacher "Y" is added.
Teacher "Y" is going to take the "ID" of Teacher "X".
The result is that the ( class_ID 2 and 3) are attributed to teacher "Y".
Thus, I think the only solution is to set the "ID" field AUTOINCREMENT in "tbl_teacher_subject".
What do you think sir?
thank you

[Zvoni]

To get the first thing out of the way (Teacher is paid full).

You're missing that you already get the information with the Query for the teachers payment!

You compare Sum(Payment) against TotalPrice. Why?

I compare TotalTeacherPriceRemaining =0 (or <=0) <-- you already get the "Remaining" shipped with the first query

WITH 
    SC    AS (SELECT SC.Class_ID, SUM(COALESCE(SC.Price, S.SubjectPrice)*SC.NumOfSessions) As TotalPrice, 
                TS.TeacherPercentage, SUM(SC.NumOfSessions) As TotalSessions
                FROM tbl_student_class As SC
                INNER JOIN tbl_teacher_subject AS TS ON TS.ID=SC.Class_id
                INNER JOIN tbl_subject AS S ON S.ID=TS.Subject_ID
                GROUP BY SC.Class_ID, TS.TeacherPercentage),
    CT      AS (SELECT S.SName As SubjectName, SC.TotalPrice, T.TName As TeacherName, SC.TeacherPercentage, 
                SC.TotalPrice*TS.TeacherPercentage/100 As TeacherTotalPrice,
                SC.TotalSessions, COALESCE(TR.Payment,0) AS Payment,
                SC.TotalPrice*TS.TeacherPercentage/100-SUM(COALESCE(TR.Payment,0)) OVER(PARTITION BY SC.Class_ID ORDER BY TR.PayDate) As TotalTeacherPriceRemaining,
                TR.Paydate 
                FROM tbl_subject As S 
                INNER JOIN tbl_teacher_subject As TS ON TS.Subject_ID=S.ID 
                INNER JOIN tbl_teacher AS T ON T.ID=TS.Teacher_ID 
                LEFT JOIN SC ON SC.Class_ID=TS.ID
                LEFT JOIN tbl_transaction As TR ON TR.Teacher_Subject_ID=TS.ID)
                
SELECT CT.SubjectName, CT.TotalPrice, CT.TeacherName, CT.TeacherPercentage, CT.TeacherTotalPrice, 
SUM(CT.Payment) As SumPayment, MIN(CT.TotalTeacherPriceRemaining) As TotalTeacherPriceRemaining, MAX(CT.PayDate) As PayDate 
FROM CT 
GROUP BY CT.SubjectName, CT.TotalPrice, CT.TeacherName, CT.TeacherPercentage, CT.TeacherTotalPrice
HAVING CT.TotalTeacherPriceRemaining=0 /* or <=0 if Payment overshoots */

Returns (i changed my data in tbl_student_class ID=1 to get to zero)

SubjectName	TotalPrice	TeacherName	TeacherPercentage	TeacherTotalPrice	SumPayment	TotalTeacherPriceRemaining	PayDate
Physics	110.0	James Cameron	50.0	55.0	55.0	0.0	2023-03-15

[Zvoni]

Hello Zvoni
I've just noticed an issue.
I think that the "tbl_teacher_subject" should have the "ID" field AUTOINCREMENT .
Let's imagine this scenario:
Teacher "X" has been attributed (Class_ID 2 and 3) in tbl_student_class"
Then Teacher "X" is deleted in "tbl_teacher_subject" and a teacher "Y" is added.
Teacher "Y" is going to take the "ID" of Teacher "X".
The result is that the ( class_ID 2 and 3) are attributed to teacher "Y".
Thus, I think the only solution is to set the "ID" field AUTOINCREMENT in "tbl_teacher_subject".
What do you think sir?
thank you

hmm.....you're right. There is an Edge-Case where you need AUTOINCREMENT, and that is if the last entry gets deleted.

BUT!!
If you look closely at my CREATE TABLE-Statement for tbl_teacher_subject

CREATE TABLE "tbl_teacher_subject" (
    "ID"    INTEGER,
    "Teacher_ID"    INTEGER,
    "Subject_ID"    INTEGER,
    "TeacherPercentage"    REAL NOT NULL,
    PRIMARY KEY("ID"),
    FOREIGN KEY("Subject_ID") REFERENCES "tbl_subject"("ID") ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY("Teacher_ID") REFERENCES "tbl_teacher"("ID") ON UPDATE CASCADE ON DELETE RESTRICT
)

you will notice something "peculiar" with the last Foreign Key
FOREIGN KEY("Teacher_ID") REFERENCES "tbl_teacher"("ID") ON UPDATE CASCADE ON DELETE RESTRICT

Look at ON DELETE RESTRICT
Try it out in a local SQLite-Client
Create tbl_teacher, tbl_subject and tbl_teacher_subject with my Constraints and then try to delete a teacher

CREATE TABLE "tbl_subject" (
    "ID"    INTEGER,
    "SName"    TEXT NOT NULL,
    "SubjectPrice"    REAL NOT NULL,
    PRIMARY KEY("ID")
);
CREATE TABLE "tbl_teacher" (
    "ID"    INTEGER,
    "TName"    TEXT NOT NULL,
    PRIMARY KEY("ID")
);
CREATE TABLE "tbl_teacher_subject" (
    "ID"    INTEGER,
    "Teacher_ID"    INTEGER,
    "Subject_ID"    INTEGER,
    "TeacherPercentage"    REAL NOT NULL,
    PRIMARY KEY("ID"),
    FOREIGN KEY("Subject_ID") REFERENCES "tbl_subject"("ID") ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY("Teacher_ID") REFERENCES "tbl_teacher"("ID") ON UPDATE CASCADE ON DELETE RESTRICT
);

[wqweto]

Btw, ON DELETE/UPDATE RESTRICT is default in most RDBMS so no need to be explicit about it. (In sqlite there are DEFERRABLE constraints so this is not exactly true for such FKs.)

cheers,
</wqw>

[Zvoni]

Btw, ON DELETE/UPDATE RESTRICT is default in most RDBMS so no need to be explicit about it. (In sqlite there are DEFERRABLE constraints so this is not exactly true for such FKs.)

cheers,
</wqw>

Not going to argue, but i rarely rely on "defaults" in such cases.
If for whatever reason i have to look at the DDL-Statement, it's clear what the intention is

[newbie2]

Zvoni
thank you very much
I'm following your guidelines but I still can delete records in tbl_teacher_subject though they have reference in tbl_student_class.
Of course I can't do that manually, but with code I can do.

[Zvoni]

Zvoni
thank you very much
I'm following your guidelines but I still can delete records in tbl_teacher_subject though they have reference in tbl_student_class.
Of course I can't do that manually, but with code I can do.

Because in the "original" DDL for "tbl_student_class" i don't have the RESTRICT constraint

CREATE TABLE "tbl_student_class" (
    "ID"    INTEGER,
    "Student_ID"    INTEGER,
    "Class_ID"    INTEGER,
    "Price"    REAL DEFAULT NULL,
    PRIMARY KEY("ID"),
    FOREIGN KEY("Class_ID") REFERENCES "tbl_teacher_subject"("ID") ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY("Student_ID") REFERENCES "tbl_student"("ID") ON UPDATE CASCADE ON DELETE CASCADE
);

FOREIGN KEY("Class_ID") REFERENCES "tbl_teacher_subject"("ID") ON UPDATE CASCADE ON DELETE CASCADE,

Change ON DELETE CASCADE there to ON DELETE RESTRICT, you won't be able to delete an entry from tbl_teacher_subject if you have an Entry in tbl_student_class

[newbie2]

I dropped the tbl_student_class and created a new table using this code but
I still can delete a record in tbl_teacher_subject that has an entry in tbl_student_class.

CREATE TABLE "tbl_student_class" (
    "ID"    INTEGER,
    "Student_ID"    INTEGER,
    "Class_ID"    INTEGER,
    "Price"    REAL DEFAULT NULL,
    PRIMARY KEY("ID"),
    FOREIGN KEY("Class_ID") REFERENCES "tbl_teacher_subject"("ID") ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY("Student_ID") REFERENCES "tbl_student"("ID") ON UPDATE CASCADE ON DELETE CASCADE
);

I even add this code but in vain
PRAGMA foreign_keys = ON