never mind my previous question, because you just check "parent".

Thanks.

Does your first query works for the child class under parent child relationship? I don't think there is an ID Key for the child table, but the first query still return the child table.

I got below working but I doubt this is really what I want. Although the query is accepted, I don't see how could I specify constraint name and 

real key property simultaneously. 

[SQL]SAMPLES>>ALTER TABLE TPCD.NATION ADD FOREIGN KEY (N_REGIONKEY) references TPCD.REGION(R_REGIONKEY);
87.    ALTER TABLE TPCD.NATION ADD FOREIGN KEY (N_REGIONKEY) references TPCD.REGION(R_REGIONKEY);

0 Rows Affected

Below is what I want:

ALTER TABLE TPCD.NATION ADD FOREIGN KEY NATION_FK2 (N_REGIONKEY2) references TPCD.REGION(R_REGIONKEY);

"NATION_FK2" is the constraint name and "N_REGIONKEY2" is the key property pointing to  TPCD.REGION.

Take another sample from your documentation:

PLEASE provide a working ALTER TABLE DDL which specifies both "StateFKey" and "State" in the query .

Class MyApp.Company Extends %Persistent 
{

Property State As %String;

ForeignKey StateFKey(State) References StateTable(StateKey);

}

Thanks, I think sth really confused is the difference between "%NOFLATTEN" and "%NOMERGE".

Is there a way to know the process Id of the given $zf(-2) on the *nix platform?

I would like kill this process after some conditions satisfied (or if there is other way to kill the process without knowing its pid).

Thanks. 

The JOIN conditions in your sample does not match my original query.

The sample you provide is P.id  = P1.id and P.id = P2.id,

in the previous sample, it should be like P.id = P1.id and P1.id =  P2.id.

Also, you self join on the identical fields "ID", so I guess the engine could infer p1.id = p2.id by "P.id  = P1.id and P.id = P2.id" , this would interfere my intention.

That's also the reason I explicitly specify two different fields "x" and "y" in my sample.  

I am not good at relational theory, but does the associativity really hold when the LEFT and INNER coexist?  

Thanks, that would work, however, I doubt its correctness.

I am kind of confused about the how Cache SQL handles the associativity between the different the join types.

If every join is inner join, that's fine, we could start from any table as the initial table to do the join.

However, in my query, there is one left join and one inner join. 

In this case, I don't expect Sample.Company could be the initial table to perform the join.

Below is the plan Cache SQL output:

• Read master map Sample.Company.IDKEY, looping on ID.

• For each row: - Read master map Sample.Employee.IDKEY, using the given idkey value.

                                - Read master map Sample.Vendor.DataMasterMap, using the given idkey value.

                                 - Output the row.

Any comment on that ? Thank for your help.