7.21 Calculate Cartesian product using left join
Use left join to calculate Cartesian product on two associated tables.
Find the age group to which each community resident belongs according to the associated Community table and Age table.
Community:
| ID | Name | Age |
|---|---|---|
| 1 | David | 28 |
| 2 | Daniel | 15 |
| 3 | Andrew | 65 |
| 4 | Rudy |
Age:
| Group | Start | End |
|---|---|---|
| Children | 0 | 15 |
| Youth | 16 | 40 |
| Middle | 41 | 60 |
| Old | 61 | 100 |
The xjoin() function works with @1 function to use left join to calculate Cartesian product.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query(“select * from Community”) |
| 3 | =A1.query@x(“select * from Age”) |
| 4 | =xjoin@1(A2:Person; A3:Age, A3.Start<=Person.Age && A3.End>=Person.Age) |
| 5 | =A4.new(Person.ID:ID, Person.Name:Name, Person.Age:Age,Age.Group:Group) |
A1 Connect to the database.
A2 Query Community table.
A3 Query Age table.
A4 Use xjoin@1() function to calculate Cartesian product using left join, and get records whose ages belong to corresponding age groups.
A5 Create a table sequence and return age group for each resident.
Execution result:
| ID | Name | Age | Group |
|---|---|---|---|
| 1 | David | 28 | Youth |
| 2 | Daniel | 15 | Children |
| 3 | Andrew | 65 | Old |
| 4 | Rudy | (null) | (null) |
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
