7.5 Association query: left join on multiple fields
Left join two associated table on the multi-field primary key and select target records.
Find name of products that have payments in the year 2014 and for which amount of a single order is above 500 based on the associated Product table, Detail table and Payment table.
SPL uses A.join() function to perform an outer join on the multi-field primary key.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query(“select * from Detail”) |
| 3 | =A1.query(“select * from Payment”) |
| 4 | =A1.query@x(“select * from Product”) |
| 5 | =A2.switch@i(ProductID,A4:ID) |
| 6 | =A3.join(OrderID:OrderNumber,A5:ID:Number,~:Detail) |
| 7 | =A6.select(year(Date)==2014 && Detail.Amount>500) |
| 8 | =A7.new(ID,Date,Detail.Product.Name:Name,Detail.Amount:Amount) |
A1 Connect to the database.
A2 Query Detail table.
A3 Query Payment table.
A4 Query Product table.
A5 Use switch@i function to convert ProductID field values in Detail table to corresponding records of Product table while deleting the unmatched Detail records.
A6 Use A.join() function to join Detail table and Payment table.
A7 Select records that have payments in the year 2014 and where the order amount is above 500.
A8 Create a new table sequence based on the selected records.
Execution result:
| ID | Date | Name | Amount |
|---|---|---|---|
| 10979 | 2014/03/26 | Soda water | 1317 |
| 11011 | 2014/04/09 | Espresso | 530 |
| … | … | … | … |
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
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
