12.9 Judge nonexistence of data matching
Based on association of two tables, get records through judging nonexistence of matching.
According to Student table and Score table, find students whose scores of all subjects are above 80.
| Student |
|---|
| ID |
| Class |
| Name |
| … |
| Score |
|---|
| StudentID |
| Subject |
| Score |
| … |
We just need to find students who haven’t any subject whose score is below 80.
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@d(ID, A5) |
A1 Connect to the database.
A2 Query Student table.
A3 Query Score table.
A4 Get Score records where scores are not above 80.
A5 Perform distinct on StudentID on the selected Score records.
A6 The A.join@d() function gets non-matching records.
Execution result:
| ID | Class | Name |
|---|---|---|
| 2 | Class 1 | Ashley |
| 16 | Class 2 | Alexis |
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
