3.13 Belong to & Contain: find if a record exists by primary key values
This happens when we are trying to find records of a sub table that cannot match any record in the primary table. There are Product table and Category table that are associated to each other and we are trying to find products whose categories do not exist in the Category table.
| Product |
|---|
| ID |
| Name |
| CategoryID |
| … |
| Category |
|---|
| ID |
| Name |
| Description |
| … |
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query(“select * from Category”).keys(ID) |
| 3 | =A1.query@x(“select * from Product”) |
| 4 | =A3.select(A2.pfind(CategoryID)==0) |
A1 Connect to the database.
A2 Query Category table and set ID as the primary key.
A3 Query Product table.
A4 pfind function searches for ordinal numbers of Category records whose primary key values match CategoryID in Product table and return 0 if no matching CategoryID can be found. Then select function gets records from Product table whose CategoryIDs do not exist in Category table.
Execution result:
| ID | Name | CategoryID | … |
|---|---|---|---|
| 12 | German cheese | … | |
| 26 | Spun sugar | 9 | … |
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
