7.1 Association query: join two tables on single fields while filtering joined records
Select target records from two tables associated on the join condition and perform further computations.
Find the number of students who select the Matlab course according to the mutually associated Course table and SelectCourse table.
A.join() function cooperates with @i option to delete the non-matching records.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query(“select * from Course”) |
| 3 | =A1.query@x(“select * from SelectCourse”) |
| 4 | =A2.select(Name==“Matlab”) |
| 5 | =A3.join@i(CourseID,A4:ID).count() |
A1 Connect to the database.
A2 Query Course table.
A3 Query SelectCourse table.
A4 Select records of the specified course from SelectCourse table.
A5 Use @i option in join() function to perform an inner join and then count.
Execution result:
| Value |
|---|
| 5 |
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
