Query through table association

Besides detail data queries on a single table, multi-table join queries are involved on many occasions. DQL supports this type of join queries, too. We can create transparent multi-table joins using methods like attributized foreign key and equivalent homo-dimension tables on advanced metadata design interface. Users just need to tune up the DQL statement to achieve a join query.

There is the complete metadata file demo.glmd in【esProc installation directory】\esProc\esproc-services\datalogic\conf. The file contains definitions of pseudo tables, DQL tables transformed from pseudo tables and relationships between tables. Following illustrations still take the metadata file demo.glmd as an example.

 

The above displays three tables – Orders, Customer and City. Orders and Customer are associated through CustomerID and Customer and City are related via CityCode. How to query orders information of clients in different cities according to these associations? If a businessperson needs to observe orders information in different time periods based on the year, month and day, how should they operate with DQL?

Still take the metadata file demo.glmd as an example, let’s look at the process of achieving such a computing need, during which you’ll learn how to use The DQL foreign-key-based queries and multilevel queries.

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.

Multilevel query

Click  to add three logical tables – Year, YearMonth and Day (See Logical table level function to learn more about logical tables).

 

 

 

Add a level function for Day dimension:

Add foreign key association to Orders table. The foreign key name is fk3, foreign key field is ShipDate, and the refrenced field is Day table’s Day field:

Save the metadata file and click Tool - DQL query through the menu bar to perform aggregation over data on the year level of Orders table using the syntax【foreign key#level function】. The DQL statement and result are as follows:

DQL：SELECT sum(Amount) Total ON Year FROM Orders BY ShipDate#Year

 

See Join query syntax to find more related query syntax.