support
buy cialis buy valium online buy viagra online

Code Examples of Foreign Key Function in esProc

If field (or combination of fields) k is table M’s primary key, and k, at the same time, exists in table B, then k is regarded as the foreign key of B. The foreign key maintains a relationship between two tables and is one of the most important concepts in structured data computing. Through object references, esProc makes foreign key easy to function. The following examples aim to illustrate the operation.

Example 1 Create relationship between a referenced table and a referencing table

order is the referencing table, emp is the referenced table, connect emp to order. Display emp’s Name field, Gender field and Salary field and order’s OrderID field and Amount field.

Note: Only tables emp and order are used here. Later, table dep is to be used in other examples. The relationship between the three tables over foreign key is shown as follows:

esProc_foreign_key_1

Data may originate from databases or texts. For example:

         order=esProc.query(“select OrderID,SellerId,Amount,OrderDate from sales”)

         emp=esProc.query(“select EId,Name,Gender,Birthday,Dept,Salary from emp”)

         dep=esProc.query(“select * from department”)

esProc code:

A3=order.switch(SellerId, emp:EId)

         A4=order.new(OrderID,Amount,SellerId.Name,SellerId.Gender,SellerId.Salary)

Computed results:

esProc_foreign_key_2

Code interpretation:

A3: Replace records of SellerID in order with their counterparts in emp, create foreign key relationship between the two tables.

A4: Get OrderID field and Amount field in order, and get Name, Gender and Salary field in emp through related references. We can see that, with object references, fields in emp can be accessed to directly from order. Thus complex and difficult join statements can be skipped.

Example 2: Query referencing table according to condition of referenced table

Find orders signed by female sellers whose salary is greater than 10,000.

esProc code:

         A3=order.switch(SellerId, emp:EId)         / the same as above example

         A5=order.select(SellerId.Salary>10000 && SellerId.Gender==”F”)

Computed results:

esProc_foreign_key_3

Click the above blue hyperlinks and corresponding employee information will be shown:

esProc_foreign_key_4

Example 3: Group according to referenced table

Compute sales amount of each department.

esProc code:

   A3=order.switch(SellerId, emp:EId)         / the same as above example

         A5=order.groups(SellerId.Dept;sum(Amount))

Computed results:

esProc_foreign_key_5

Field names can be changed, like order.groups(SellerId.Dept:dt;sum(Amount):amt). The effect of name change is shown below:

esProc_foreign_key_6

Example 4: Complex association between multiple tables

For departments whose sales amount is greater than 50,000, find their managers’ names.

esProc code:

         A3=order.switch(SellerId, emp:EId)        

         A4=dep.switch(Manager,emp:EId)

         A5=emp.switch(Dept,dep:DeptNo)

         A6=order.groups(SellerId.Dept:dt;sum(Amount):amt)

         A7=A6.select(amt<=50000).(dt).(Manager).(Name)

Computed results:

esProc_foreign_key_7

Code interpretation:

A3, A4, A5:Create complete foreign key relationship.

A6: Compute sales amount of each department (See the above example). See below:

esProc_foreign_key_8

A7:Use object references to solve the question in tuitionally. Expression A6.select(amt<=50000).(dt).(Manager).(Name)can be divided into four steps according to full stops. See below:

a. Find records whose sales amount is greater than 50,000 in A6.

b. Get records corresponding to dt field (in table dep)

c. Get records corresponding to Manager field (in table emp)

d. Get Name field.

Details are shown below:

A6.select(amt<=50000)

esProc_foreign_key_10

.(dt)

esProc_foreign_key_9

.(Manager)

esProc_foreign_key_11

.(Name) 

esProc_foreign_key_12

replica franck-muller watches  replica hublot watches  replica panerai watches  replica bvlgari watches  replica patek-philippe watches  replica montblanc watches  replica longines watches  replica hermes watches  replica audemars piguet watches  replica vacheron constantin watches