7.2 Association query: join two tables on multiple fields while filtering joined records
Select target records from two tables associated on the multi-field join condition and then perform grouping & aggregation.
Find the total score of all subjects for each student in class one according to the mutually associated Score table and Student table.
A.join()function cooperates with @i option to delete the non-matching records. The operation specifies the Class value as a constant condition (which is Class one) to achieve the inner join.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query(“select * from Score”) |
| 3 | =A1.query@x(“select * from Student”) |
| 4 | =A2.join@i(ID:“Class one”, A3:ID:Class) |
| 5 | =A4.groups(ID; sum(Score):TotalScore) |
A1 Connect to the database.
A2 Query Score table.
A3 Query Student table.
A4 The join function works with @i option to perform the multi-field inner join on ID and Class.
A5 Group joined records on student ID and calculate total score of all subjects for each student.
Execution result:
| ID | TotalScore |
|---|---|
| 1 | 230 |
| 2 | 258 |
| 3 | 228 |
| … | … |
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
