7.3 Association query: multi-table join
Based on three associated tables, select target records according to the join fields and perform computations.
Find orders that not have all payments according to the associated Detail table, Order table and Payment table.
Use join() function to join the three tables.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query(“select * from Order”) |
| 3 | =A1.query(“select * from Detail”) |
| 4 | =A1.query@x(“select * from Payment”) |
| 5 | =A3.group(ID) |
| 6 | =A4.group(ID) |
| 7 | =join(A2:Order,ID; A5:Detail,ID; A6:Payment,ID) |
| 8 | =A7.new(Order.ID:ID,Detail.sum(Amount):Amount,Payment.sum(Amount):Pay) |
| 9 | =A8.select(Pay<Amount) |
A1 Connect to the database.
A2 Query Order table.
A3 Query Detail table.
A4 Query Payment table.
A5 Group Detail table on order ID.
A6 Group Payment table on order ID.
A7 Use join function to join Order table, Detail table and Payment table on order ID.
A8 Create a table sequence and calculate amount of each order and their paid amount.
A9 Get records where the paid amount is less than the order amount.
Execution result:
| ID | Amount | Pay |
|---|---|---|
| AROUT | 55492.0 | 35980 |
| BERGS | 3398.55 | 1080 |
| … | … | … |
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
