Foreign-key-based queries

In “DQL –> Table –> Foreign key” tab, click  button to append a row and specify a foreign key.

To create association between Orders table, Customer table and City table, for example, add foreign key named fk1 to Orders table. Its foreign key table is Customer, whose foreign key field is CustomerID:

Add foreign key to Customer and name it fk_City. Its foreign key table is City, whose foreign key field is CityCode:

Save the metadata file, click Tool>DQL query through the menu bar and query data from Customer and City through the syntax【foreign key field. foreign key table field】. The DQL statement and result are as follows:

DQL: SELECT OrderID,CustomerID.CustName,CustomerID.CityCode.City,EmployeeID,ReceiveDate,ShipDate,Amount FROM Orders

During a foreign-key-based query, we can also use the syntax【foreign key name.foreign key table field】to query the target data. For the above query, the DQL statement and result are as follows:

DQL: SELECT OrderID,fk1.CustName,fk1.fk_City.City,EmployeeID,ReceiveDate,ShipDate,Amount FROM Orders

See Join query syntax to find more related query syntax.