12.3 Judge existence of foreign key mapping
Based on association of two tables, get records through judging existence of foreign key mapping.
According to Course table and SelectCourse table, find how many students in each class select the Matlab” course.
| Course |
|---|
| ID |
| Subject |
| Score |
| SelectCourse |
|---|
| ID |
| Class |
| Name |
Here A.join() function works with @i option to delete 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(ID, A4:ID) |
| 6 | =A5.groups(Class; count(1):SelectCount) |
A1 Connect to the database.
A2 Query Course table.
A3 Query SelectCourse table.
A4 Get records of the specified course from Course table.
A5 The join() function works with @i option to perform a filtering join.
A6 Group joining result by Class and count students who select the Matlab” course.
Execution result:
| Class | SelectCount |
|---|---|
| Class 1 | 3 |
| Class 2 | 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
