To Query Multiple Tables

You construct business query language (BQL) statements that join multiple tables by using one of the Join clauses in one of the PXSelect classes that has the Join type parameter. (For more information on selecting the PXSelect class, see To Select Records By Using Traditional BQL.)

In BQL statements, you can join multiple database tables by using the following clauses directly mapped to SQL JOIN clauses:

  • InnerJoin returns all records where there is at least one match in both tables.
  • LeftJoin returns all records from the left table, and the matched records from the right table. Where there are no matched records from the right table, null values are inserted.
  • RightJoin returns all records from the right table, and the matched records from the left table. Where there are no matched records from the left table, null values are inserted.
  • FullJoin returns all records when there is a match in one of the tables.
  • CrossJoin returns the entire Cartesian product of the two tables.

To Join Two Tables (Inner Join, Left Join, Right Join, or Full Join)

To join two tables, use one of the Join clauses with two type parameters (such as InnerJoin<Table, On>) and the On<Operand, Comparison> or On<Operator> class to specify a conditional expression for joining, as shown in the following sample BQL statement. 

PXSelectJoin<SalesOrder,
    InnerJoin<OrderDetail,
        On<OrderDetail.orderNbr, Equal<SalesOrder.orderNbr>>>>
Note:
MYOB Acumatica Framework translates the previous BQL statement to the following SQL query, where [list of columns] is the list of columns of the joined tables.
SELECT [list of columns] FROM SalesOrder
INNER JOIN OrderDetail
    ON OrderDetail.OrderNbr = SalesOrder.OrderNbr

MYOB Acumatica Framework explicitly enumerates the columns of the database table in the SQL query. For details on which columns are enumerated, see Translation of a BQL Command to SQL.

To Cross-Join Two Tables

To cross-join two tables, use the CrossJoin<Table> class, as shown in the following sample BQL statement.

PXSelectJoin<Product, CrossJoin<Supplier>>
Note:
MYOB Acumatica Framework translates the previous BQL statement to the following SQL query, where [list of columns] is the list of columns of the joined tables.
SELECT [list of columns] FROM Product CROSS JOIN Supplier

MYOB Acumatica Framework explicitly enumerates the columns of the database table in the SQL query. For details on which columns are enumerated, see Translation of a BQL Command to SQL.

To Join Multiple Tables

To specify multiple join clauses, use the following instructions:

  • Use a Join clause with three type parameters (such as InnerJoin<Table, On, NextJoin>). Each subsequent join clause is specified as the last type parameter of the previous join clause, as shown in the following sample BQL statement.

    PXSelectJoin<SalesOrder,
    InnerJoin<OrderDetail,
        On<OrderDetail.orderNbr, Equal<SalesOrder.orderNbr>>,
    LeftJoin<Employee,
        On<Employee.employeeID, Equal<SalesOrder.employeeID>>>>>
    Note:
    MYOB Acumatica Framework translates this BQL statement to the following SQL query, where [list of columns] is the list of columns of the joined tables. 
    SELECT [list of columns] FROM SalesOrder
INNER JOIN OrderDetail
    ON OrderDetail.OrderNbr = SalesOrder.OrderNbr
LEFT JOIN Employee
    ON Employee.EmployeeID = SalesOrder.EmployeeID

    MYOB Acumatica Framework explicitly enumerates the columns of the database table in the SQL query. For details on which columns are enumerated, see Translation of a BQL Command to SQL.

  • Use the On conditions to specify conditional expressions for joining, as shown in the following sample BQL statement. In subsequent join clauses, the On conditions can refer to fields from any joined table, and can contain any number of conditions chained by logical operators as in filtering conditions.

    PXSelectJoin<SalesOrder,
    InnerJoin<OrderDetail,
        On<OrderDetail.orderNbr, Equal<SalesOrder.orderNbr>>,
    LeftJoin<Employee,
        On<Employee.employeeID, Equal<SalesOrder.employeeID>>,
    RightJoin<Product,
        On<Product.productID, Equal<OrderDetail.productID>,
        And<Product.unitPrice, Equal<OrderDetail.unitPrice>>>>>>>
    Note:
    MYOB Acumatica Framework translates the previous BQL statement to the following SQL query, where [list of columns] is the list of columns of the joined tables. 
    SELECT [list of columns] FROM SalesOrder
INNER JOIN OrderDetail
    ON OrderDetail.OrderNbr = SalesOrder.OrderNbr
LEFT JOIN Employee
    ON Employee.EmployeeID = SalesOrder.EmployeeID
RIGHT JOIN Product
    ON (Product.ProductID = OrderDetail.ProductID AND
        Product.UnitPrice = OrderDetail.UnitPrice)

    MYOB Acumatica Framework explicitly enumerates the columns of the database table in the SQL query. For details on which columns are enumerated, see Translation of a BQL Command to SQL.