7.13 Cascade association
Perform filtering on two associated tables through cascade association relationships.
Find the American employees whose managers are Chinese according to the associated Employee table and Department table.
SPL offers A.switch() function to convert foreign key field values to matching records in the foreign key table.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query(“select * from Employee”) |
| 3 | =A1.query@x(“select * from Department”) |
| 4 | =A3.switch(Manager, A2:ID) |
| 5 | =A2.switch(Dept, A4:ID) |
| 6 | =A5.select(Nation==“American” && Dept.Manager.Nation==“Chinese”) |
A1 Connect to the database.
A2 Query Employee table.
A3 Query Department table.
A4 Use switch() function to transform Manager field values in Department table to matching Employee records.
A5 Use switch() function to transform Dept field values in Employee table to matching Department records.
A6 Get records of employees whose nations are American and whose managers are Chinese.
Execution result:
| ID | Name | Nation | Dept |
|---|---|---|---|
| 11 | Simon | American | 2 |
| 103 | Rudy | American | 2 |
| … | … | … | … |
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
