# Thread: Another complicated query but perhaps possible

1. ## Re: Another complicated query but perhaps possible

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.
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

2. ## Re: Another complicated query but perhaps possible

Originally Posted by 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.
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

3. ## Re: Another complicated query but perhaps possible

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

4. ## Re: Another complicated query but perhaps possible

I think no escape of a second trasaction table.

5. ## Re: Another complicated query but perhaps possible

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?

6. ## Re: Another complicated query but perhaps possible

Originally Posted by newbie2
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

7. ## Re: Another complicated query but perhaps possible

Originally Posted by newbie2
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"

Originally Posted by newbie2
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?

8. ## Re: Another complicated query but perhaps possible

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.

9. ## Re: Another complicated query but perhaps possible

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
Code:
```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
Code:
```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

10. ## Re: Another complicated query but perhaps possible

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.

11. ## Re: Another complicated query but perhaps possible

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.
Code:
```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

12. ## Re: Another complicated query but perhaps possible

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:
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

13. ## Re: Another complicated query but perhaps possible

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

Code:
```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

14. ## Re: Another complicated query but perhaps possible

Whooops!

NumOFSessions was missing above
Code:
``` 	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

15. ## Re: Another complicated query but perhaps possible

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

16. ## Re: Another complicated query but perhaps possible

You are so generous
Million thanks sir

17. ## Re: Another complicated query but perhaps possible

Zvoni Excuse me
I'm getting crasy to make the last code working.
I spent a whole night trying to understand the code.
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.
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

18. ## Re: Another complicated query but perhaps possible

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
Code:
```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

19. ## Re: Another complicated query but perhaps possible

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

Code:
```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)```

20. ## Re: Another complicated query but perhaps possible

Found it

Code:
```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

21. ## Re: Another complicated query but perhaps possible

I'm sorry sir I'm still having trouble.
I can't figure out where the problem is.
Code:
```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

22. ## Re: Another complicated query but perhaps possible

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

23. ## Re: Another complicated query but perhaps possible

Code:
```   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
End With
Cnn.CreateTable "tbl_student"
'
With Cnn.NewFieldDefs
End With
Cnn.CreateTable "tbl_subject"
'
With Cnn.NewFieldDefs
End With
Cnn.CreateTable "tbl_teacher"
''
With Cnn.NewFieldDefs
.Add "ID    INTEGER Primary Key "
.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 "Price    REAL DEFAULT NULL "
.Add "NumOfSessions INTEGER DEFAULT 1," & _
" FOREIGN KEY(Class_ID) REFERENCES tbl_teacher_subject(ID) ON UPDATE CASCADE ON DELETE CASCADE ," & _
End With
Cnn.CreateTable "tbl_student_class"

With Cnn.NewFieldDefs
.Add "ID   INTEGER PRIMARY KEY "
.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)```

24. ## Re: Another complicated query but perhaps possible

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

25. ## Re: Another complicated query but perhaps possible

Code:
```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
End With
Cnn.CreateTable "tbl_student"
'
With Cnn.NewFieldDefs
End With
Cnn.CreateTable "tbl_subject"
'
With Cnn.NewFieldDefs
End With
Cnn.CreateTable "tbl_teacher"
''
With Cnn.NewFieldDefs
.Add "ID    INTEGER Primary Key "
.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 "Price    REAL DEFAULT NULL "
.Add "NumOfSessions INTEGER DEFAULT 1," & _
" FOREIGN KEY(Class_ID) REFERENCES tbl_teacher_subject(ID) ON UPDATE CASCADE ON DELETE CASCADE ," & _
End With
Cnn.CreateTable "tbl_student_class"

With Cnn.NewFieldDefs
.Add "ID   INTEGER PRIMARY KEY "
.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)```

26. ## Re: Another complicated query but perhaps possible

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

27. ## Re: Another complicated query but perhaps possible

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.

28. ## Re: Another complicated query but perhaps possible

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)

29. ## Re: Another complicated query but perhaps possible

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.
Code:
```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)
Code:
```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

30. ## Re: Another complicated query but perhaps possible

Will be on monday

31. ## Re: Another complicated query but perhaps possible

Originally Posted by Zvoni
Will be on monday
Thank you very much sir

32. ## Re: Another complicated query but perhaps possible

I think I got it but I need your confirmation
Code:
```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```

33. ## Re: Another complicated query but perhaps possible

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

34. ## Re: Another complicated query but perhaps possible

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

Code:
```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

35. ## Re: Another complicated query but perhaps possible

Originally Posted by 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
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

Code:
```CREATE TABLE "tbl_teacher_subject" (
"ID"    INTEGER,
"Teacher_ID"    INTEGER,
"Subject_ID"    INTEGER,
"TeacherPercentage"    REAL NOT NULL,
PRIMARY KEY("ID"),
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

Code:
```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("Teacher_ID") REFERENCES "tbl_teacher"("ID") ON UPDATE CASCADE ON DELETE RESTRICT
);```

36. ## Re: Another complicated query but perhaps possible

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>

37. ## Re: Another complicated query but perhaps possible

Originally Posted by 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>
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

38. ## Re: Another complicated query but perhaps possible

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.

39. ## Re: Another complicated query but perhaps possible

Originally Posted by 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.
Because in the "original" DDL for "tbl_student_class" i don't have the RESTRICT constraint
Code:
```CREATE TABLE "tbl_student_class" (
"ID"    INTEGER,
"Student_ID"    INTEGER,
"Class_ID"    INTEGER,
"Price"    REAL DEFAULT NULL,
PRIMARY KEY("ID"),
);```

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

40. ## Re: Another complicated query but perhaps possible

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.
Code:
```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,
);```
I even add this code but in vain
PRAGMA foreign_keys = ON

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•