12.6 Judge existence of composite foreign key mapping
Based on association of two tables, get records thought judging existence of composite foreign key mapping.
According to Score table and Student table, calculate average score of boys in class one.
| Score |
|---|
| StudentID |
| Class |
| Subject |
| Score |
| Student |
|---|
| ID |
| Class |
| Name |
| Gender |
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query(“select * from Score”) |
| 3 | =A1.query@x(“select * from Student”) |
| 4 | =A3.select(Class==“Class 1” && Gender==“Male”) |
| 5 | =A2.join@i(Class:StudentID, A4:Class:ID) |
| 6 | =A5.groups(StudentID; avg(Score):Score) |
A1 Connect to the database.
A2 Query Score table.
A3 Query Student table.
A4 Select Student records of boys in class one.
A5 The A.join@i() function performs filtering join.
A6 Group join result by StudentID and calculate average score of each student.
Execution result:
| StudentID | Score |
|---|---|
| 1 | 76 |
| 3 | 74 |
| … | … |
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
