11.11 Search for all upper-level references

 

Perform self-join on a single table and find all upper-level references.
Based on China administrative division table, list all superior regions for each division. For Shijiazhuang, for instance, the output is China,Hebei,Shijiazhuang.

ID NAME PARENT_ID
1 China 0
11 Beijing 1
12 Tianjin 1
13 Hebei 1
1301 Shijiazhuang 13
1302 Tangshan 13

SPL uses P.nodes@p(F,r) function to search for all upper-level references.

SPL script:

A
1 =connect(“db”)
2 =A1.query@x(“select * from ChinaRegion”)
3 >A2.switch(PARENT_ID,A2:ID)
4 =A2.nodes@p(PARENT_ID)
5 =A4.run(=.(NAME).concat@c())

A1 Connect to the database.
A2 Query ChinaRegion table.
A3 Map foreign key PARENT_ID to the corresponding record to achieve self-join.
A4 The nodes() function works with @p option to recursively search for all upper-level references.
A5 Concatenate upper-level reference names, separated with comma.

Execution result:

A4:

Member
[[1,China,]]
[[1,China,],[11,Beijing,]]
[[1,China,],[12,Tianjin,]]
[[1,China,],[13,Hebei,]]
[[1,China,],[13,Hebei,],[1301,Shijiazhuang,]]
[[1,China,],[13,Hebei,],[1302,Tangshan,]]
[[1,China,],[13,Hebei,],[1303,Qinhuangdao]]
[[1,China,],[13,Hebei,],[1301,Shijiazhuang,],…]
[[1,China,],[13,Hebei,],[1301,Shijiazhuang,],…]
[[1,China,],[13,Hebei,],[1301,Shijiazhuang,],…]

A5:

Member
China
China,Beijing
China,Tianjin
China,Hebei
China,Hebei,Shijiazhuang
China,Hebei,Tangshan
China,Hebei,Qinhuangdao
China,Hebei,Shijiazhuang,Chang’an District
China,Hebei,Shijiazhuang,Qiaoxi District
China,Hebei,Shijiazhuang,Xinhua District