7.4 Association query: left join on single fields
Left join two associated table to achieve cross-table computations.
Find the total evaluation for each student according to the associated Student table an Evaluation table. The base score of each student is 70 and they get their point increased or deducted according to the evaluation in Evaluation table.
The join() function uses @1 function to perform the left join, which generates records based on the first table sequence and represents non-matching members as null.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query(“select * from Students”) |
| 3 | =A1.query@x(“select * from Evaluation”) |
| 4 | =A3.group(StudentID) |
| 5 | =join@1(A2:Students,ID;A4:Evaluation,StudentID) |
| 6 | =A5.new(Students.ID:ID,Students.Name:Name,70+Evaluation.sum(Score):Score) |
A1 Connect to the database.
A2 Query Students table.
A3 Query Evaluation table.
A4 Group Evaluation table on StudentID.
A5 Use join@1 function to left join Students table and the grouped Evaluation table.
A6 Create a table sequence and calculate the total evaluation score (base score + evaluation score) for each student.
Execution result:
| ID | Name | Score |
|---|---|---|
| 1 | Ashley | 85 |
| 2 | Rachel | 65 |
| 3 | Emily | 70 |
| … | … | … |
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
