|
|
The features on this page require a GENESIS64 Advanced license and are not available with GENESIS64 Basic SCADA . |
|
|
The features on this page require a GENESIS64 Advanced license and are not available with GENESIS64 Basic SCADA . |
A joined table is a result set that is the product of two or more tables. AnalytiX-BI supports INNER, LEFT OUTER and RIGHT OUTER joins.
The following query retrieves the quantity of products for each order using an inner join.
SELECT OrderID, ProductName, Quantity
FROM Products AS p INNER JOIN OrderDetails AS od
ON p.ProductID = od.ProductID
Note: selected columns do not have to be qualified when their name is unique among the joined tables.
The following example shows a query that uses a LEFT join to display the list of customers without any associated orders.
SELECT ContactName, OrderID
FROM Customers AS c LEFT JOIN Orders AS o
ON c.CustomerID = o.CustomerID
WHERE o.OrderID IS NULL
ORDER BY c.ContactName
Similarly, the following query uses a RIGHT join to display all order numbers that do not have a customer associated with them.
SELECT ContactName, OrderID
FROM Customers AS c RIGHT JOIN Orders AS o
ON c.CustomerID = o.CustomerID
WHERE c.ContactName IS NULL
ORDER BY o.OrderID
If relationships between tables are established in the data model, the JOIN keyword can be omitted. However, all columns in the query must be fully qualified. AnalytiX-BI will determine the join path among all the tables that are referenced in the query automatically.
The following query calculates the total for each order without having to specify explicitly the FROM or JOIN keywords for the Orders and OrderDetails tables.
SELECT Orders.CustomerID, Orders.OrderDate, SUM(OrderDetails.UnitPrice * OrderDetails.Quantity * (1 - OrderDetails.Discount)) AS OrderTotal
ORDER BY Orders.OrderDate
AnalytiX-BI automatically determines the join path between the Orders and OrderDetails table based on the relationships configured in the model.
Note: If the FROM and JOIN keywords are omitted, AnalytiX-BI automatically performs INNER joins.
Note: Although this syntax is supported for backwards compatibility, it is recommended to always specify the tables involved in the query using the FROM keyword and explicitly define the desired joins using the JOIN keyword.