12.4 Judge existence of non-foreign key matching
Based on association of two tables, get records through judging existence of non-foreign key matching.
According to Score table and Student table, find the number of students in each class whose scores of a certain subject are above 80.
| Score |
|---|
| StudentID |
| Subject |
| Score |
| Student |
|---|
| ID |
| Class |
| Name |
Perform filtering with a subquery and then distinct according to the join field on Score table, and the field will be a primary key.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query(“select * from Student”) |
| 3 | =A1.query@x(“select * from Score”) |
| 4 | =A3.select(Score>80) |
| 5 | =A4.id(StudentID) |
| 6 | =A2.join@i(ID, A5) |
| 7 | =A6.groups(Class; count(1):StudentCount) |
A1 Connect to the database.
A2 Query Student table.
A3 Query Score table.
A4 Select Score records where scores are above 80.
A5 Use id() function to perform distinct on StudentID.
A6 Use A.join@i() function to perform filtering join.
A7 Group join result by Class and count students in each class.
Execution result:
| Class | StudentCount |
|---|---|
| Class 1 | 9 |
| Class 2 | 11 |
| … | … |
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
