P.fjoin ()
Perform foreign-key-style association on a table sequence/record sequence.
Syntax:
P.fjoin(w:T,x:F,…;…)
Note:
The function computes expression w over each row of table sequence/record sequence P and then expression x on w to generate a new field F, and returns a new table sequence consisting of P and F.
T is w’s alias, which can be referenced in expression x; when x is represented by ~, it is treated as w; re-assign field F when it already exists in P.
A primary table and a subtable can be associated through the foreign key in parameter w.
Parameter:
|
P |
A table sequence/record sequence |
|
w |
An expression, which, besides the regular syntax, can be used in the following syntax: 1. K=w, which means value assignment; K is a field of P, and you can use an esProc function in w; 2. (Ki=wi,…,w), which contains the combined use of Ki=wi, where w is a logical expression, and where Ki can be referenced in w; |
|
T |
Alias of expression w; can be absent |
|
x |
An expression; can be absent |
|
F |
Field name in expression x; can be absent |
Return value:
Table sequence
Option:
|
@i |
Delete the current record when result of expression w is null or false |
|
@m |
Enable parallel processing |
Example:
When using syntax K=w:
|
|
A |
|
|
1 |
=connect("demo").query("SELECT top 20 CID,NAME,POPULATION,STATEID FROM CITIES") |
|
|
2 |
=connect("demo").query("SELECT top 20 * FROM STATECAPITAL").keys(STATEID) |
Return a table sequence whose primary key is STATEID.
|
|
3 |
=A1.fjoin(STATEID=A2.find(STATEID)) |
Use the syntax k=w to associate table A2 and table A1 through the latter’s foreign key STATEID, and return a result set by assigning A2’s corresponding referencing records to A1’s foreign key and nulls to the non-matching records.
|
When @i option is present:
|
|
A |
|
|
1 |
=connect("demo").query("SELECT top 20 CID,NAME,POPULATION,STATEID FROM CITIES") |
Return a table sequence:
|
|
2 |
=A1.fjoin@i(CID<5) |
Use @i option to get records where CID is less than 5 from table sequence A1, during which whole records will be deleted if result of the expression is null or false. |
|
3 |
=connect("demo").query("SELECT top 20 * FROM STATECAPITAL").keys(STATEID) |
|
|
4 |
=A1.fjoin@i(STATEID=A3.find(STATEID)) |
Use @i option to associate table A3 and A1 through the latter’s foreign key STATEID, during which whole records of A1 will be deleted if the foreign key value cannot find a match in A3.
|
When using syntax (Ki=wi,…,w):
|
|
A |
|
|
1 |
=demo.query("select EID,NAME,DEPT from employee") |
Return a table sequence:
|
|
2 |
=6.new(~:ID,~*~:Num).keys(ID) |
Generate a table sequence using ID as the key.
|
|
3 |
=create(name,gender).record(["Rebecca","F","Ashley","F","Matthew","M"]).keys(name) |
Generate a table sequence using name as the key:
|
|
4 |
=A1.fjoin@i((EID=A2.find(EID),NAME=A3.find(NAME),EID!=null&&NAME!=null)) |
A1 associates with A2 through foreign key field EID and with A3 through foreign key field NAME by switching EID to A2’s referencing records and NAME to A3’s referencing records, while deleting non-matching records in A1.
|
|
5 |
=A1.fjoin@i((EID=A2.pfind(EID),NAME=A3.pfind(NAME),EID!=null&&NAME!=null)) |
A1 associates with A2 through foreign key field EID and with A3 through foreign key field NAME, by assigning ordinal numbers of the corresponding key values in A2 to A1’s foreign key field EID and assigning ordinal numbers of the corresponding key values in A3 to its foreign key field NAME, while deleting non-matching records from A1.
|
When using other syntax:
|
|
A |
|
|
1 |
=demo.query("select EID,NAME,DEPT,GENDER,BIRTHDAY from employee") |
Return data of employee table:
|
|
2 |
=A1.fjoin(age(BIRTHDAY):age,age>50:ifRetire) |
Get age of each employee in table sequence A1 – use age as the alias of age computation, and then find if each age is above 50, and add results of computing expression age>50 to table sequence A1 as a new field named ifRetire:
|
|
3 |
=A1.fjoin(age(BIRTHDAY),~:AGE) |
The parameter x represented by ~ means parameter w itself, that is, AGE field contains results of computing age(BIRTHDAY):
|
|
|
A |
|
|
1 |
=demo.query("select EID,NAME,DEPT,GENDER,BIRTHDAY from employee") |
Return a table sequence:
|
|
2 |
=A1.fjoin(if(GENDER=="F","female","male"):GENDER,GENDER) |
Parameter w is the value assignment format; return female when GENDER value is F, otherwise return male; since w’s alias GENDER already exists in the table sequence, the function won’t generate a new field but assigns new values to table sequence A1’s GENDER field:
|
