11.10 Search for records containing the specified value on the reference chain and list their upper-level reference
Perform self-join on a single table, get records containing the specified value, and list their superiors.
Find subordinate administrative regions of Hebei province based on China administrative division table.
| ID | NAME | PARENT_ID |
|---|---|---|
| 1 | China | 0 |
| 11 | Beijing | 1 |
| 12 | Tianjin | 1 |
| 13 | Hebei | 1 |
| … | … | … |
| 1301 | Shijiazhuang | 13 |
| 1302 | Tangshan | 13 |
| … | … | … |
SPL has P.nodes(F,r) function to get records having the specified value on the reference chain.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query@x(“select * from ChinaRegion”) |
| 3 | >A2.switch(PARENT_ID,A2:ID) |
| 4 | =A2.select@1(name==“Hebei”) |
| 5 | =A2.nodes(PARENT_ID,A4) |
| 6 | =A5.new(ID,NAME,PARENT_ID.NAME:PARENT_NAME) |
A1 Connect to the database.
A2 Query ChinaRegion table.
A3 Map foreign key PARENT_ID to the corresponding record to achieve self-join.
A4 Get the record of Hebei province.
A5 Use nodes() function to search references recursively to get records where the direct or indirect PARENT_ID is Hebei .
A6 Create a table sequence consisting of ID, region name and upper-level region name.
Execution result:
A5:
| ID | NAME | PARENT_ID |
|---|---|---|
| 1301 | Shijiazhuang | [13,Hebei,] |
| 1302 | Tangshan | [13,Hebei,] |
| 1303 | Qinhuangdao | [13,Hebei,] |
| 1304 | Handan | [13,Hebei,] |
| 1305 | Xingtai | [13,Hebei,] |
| … | … | … |
| 130102 | Chang’an District | [1301,Shijiazhuang,] |
| 130104 | Qiaoxi District | [1301,Shijiazhuang,] |
| 130105 | Xinhua District | [1301,Shijiazhuang,] |
| 130107 | Jingxing mining area | [1301,Shijiazhuang,] |
| 130108 | Yuhua District | [1301,Shijiazhuang,] |
| 130109 | Gaocheng District | [1301,Shijiazhuang,] |
| … | … | … |
A6:
| ID | NAME | PARENT_NAME |
|---|---|---|
| 1301 | Shijiazhuang | Hebei |
| 1302 | Tangshan | Hebei |
| 1303 | Qinhuangdao | Hebei |
| 1304 | Handan | Hebei |
| 1305 | Xingtai | Hebei |
| … | … | … |
| 130102 | Changan District | Shijiazhuang |
| 130104 | Qiaoxi District | Shijiazhuang |
| 130105 | Xinhua District | Shijiazhuang |
| 130107 | Jingxing mining area | Shijiazhuang |
| 130108 | Yuhua District | Shijiazhuang |
| 130109 | Gaocheng District | Shijiazhuang |
| … | … | … |
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
