7.7 Association query: Align-join two tables on ordinal numbers
Join two associated tables through locating corresponding records on ordinal numbers.
Find all products whose corresponding category names contain “drink” based on the associated Product table and Category table.
Use A.join() function to achieve the join, during which # enables location on ordinal numbers.
SPL script:
| A | |
|---|---|
| 1 | =connect(“demo”) |
| 2 | =A1.query(“select * from Product”) |
| 3 | =A1.query@x(“select * from Category”) |
| 4 | =A2.join(CategoryID,A3:#,CName) |
| 5 | =A4.select(like@c(CName, “*drink*”)) |
A1 Connect to the data source.
A2 Query Product table.
A3 Query Category table.
A4 Use A.join()function to perform the join, during which the corresponding CategoryID value is located in the Category table on ordinal number and the foreign key field CName (category name) is defined.
A5 Select records whose category names contain “drink”, which is case insensitive.
Execution result:
| ID | Name | CategoryID | CName |
|---|---|---|---|
| 24 | Soda | 1 | Drink |
| 34 | Beer | 1 | Drink |
| 35 | Orange Juice | 1 | Drink |
| … | … | … | … |
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
