Multiple Tables In Query

Axapta query on multiple tables

Hey friends try this code to add multiple tables in query.

QueryRun selectReportQuery()

{

    Query                   query           = new Query();

    QueryRun                localQueryRun;

    QueryBuildDataSource    qbds1;

    QueryBuildDataSource    qbds2;

    QueryBuildDataSource    qbds3;

    ;

    qbds1 = query.addDataSource(tableNum(salesTable));

    qbds1.addRange(fieldNum(salesTable, affVendorAdjust)).value('1');

    qbds2 = qbds1.addDataSource(tableNum(custInvoiceJour));

    qbds2.fetchMode(JoinMode::InnerJoin);

    qbds2.relations(false);

    qbds2.addLink(fieldNum(salesTable, salesId),fieldNum(custInvoiceJour, salesId));

    qbds2.addSortField(fieldNum(CustInvoiceJour, OrderAccount), SortOrder::Ascending);

    qbds2.addSortField(fieldNum(CustInvoiceJour, InvoiceId), SortOrder::Ascending);

    qbds2.addRange(fieldNum(CustInvoiceJour, InvoiceDate)).value(queryRange(invBeginDate, invEndDate));

    qbds3 = qbds1.addDataSource(tableNum(salesLine));

    qbds3.relations(true);

    qbds3.joinMode(JoinMode::InnerJoin);

    qbds3.fetchMode(queryFetchMode::One2Many);

qbds3.addRange(fieldNum(salesLine, mbsLineVendor)).value(queryValue(vendAccount));

Here is some more code related to query in axapta

1. Sample union query from AX 2009

2. Building a query object

3. How to create a Query(dynamically)

4. Join Two Tables at Run Time

-Harry