Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Cartesian product of t...
Finance | Project Operations, Human Resources, ...
Answered

Cartesian product of three tables via Query and QueryBuildDataSource

(0) ShareShare
ReportReport
Posted on by Sebastian Aurand 1,650

Hello,

I need to connect three tables in a Query in order to get the cartesian product of all three in runtime. How can I achieve this requirenment using Query, QueryBuildDataSource and QueryRun. If I do it in the following matter the tables are fetched one after the other in the queryrun (first all values of "TableBase",  second all values of "Table1", third all values of "Table2"). The cursor "qr" never contains all the values at the same time.

Query                   queryAll  = new Query();

QueryBuildDataSource    qbds_base          = queryAll.addDataSource(tableNum(TableBase));


QueryBuildDataSource qbds_table1 = qbds_base.addDataSource(tableNum(Table1));
qbds_table1.joinMode(JoinMode::InnerJoin);

QueryBuildDataSource qbds_table2 = qbds_base.addDataSource(tableNum(Table2));
qbds_table2.joinMode(JoinMode::InnerJoin);

QueryRun qr = new QueryRun(queryAll);

while (qr.next())
{

TableBase tableBase = qr.get(tableNum(TableBase));

Table1 table1 = qr.get(tableNum(Table1));

Table2 table2 = qr.get(tableNum(Table2));

}

 

Thanks

Sebastian

  • ergun sahin Profile Picture
    ergun sahin 8,816 Moderator on at
    RE: Cartesian product of three tables via Query and QueryBuildDataSource

    Sorry,  I missed that there is no relationship between tables. Since you didn't include the code with rich formatting, I gave my full attention to understanding the codes. It seems It was necessary to focus on what you wanted to convey with the "cartesian product".

  • Sebastian Aurand Profile Picture
    Sebastian Aurand 1,650 on at
    RE: Cartesian product of three tables via Query and QueryBuildDataSource

    Thanks Martin. This was the missing point.

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 231,321 Most Valuable Professional on at
    RE: Cartesian product of three tables via Query and QueryBuildDataSource

    Ah, sorry, I got distracted by the previous answer and didn't realize that your question is different.

    Then the fact that you don't have any join conditions is correct; that's what you want.

    What if you use qbds_table1.fetchMode(QueryFetchMode::One2One) and qbds_table2.fetchMode(QueryFetchMode::One2One)? Will you get the expected result?

  • Sebastian Aurand Profile Picture
    Sebastian Aurand 1,650 on at
    RE: Cartesian product of three tables via Query and QueryBuildDataSource

    Thanks for the hints. I did not use "relations(true)" because the tables have no link. How could I establish a manual link in order to get all possible combinations in the query:

    Table Base:

    • ID1 = Entry1
    • ID2 = Entry2
    • ID3 = Entry3

    Table1:

    • IDa = First entry
    • IDb = Second entry
    • IDc = Third entry

    Table2:

    • No1 = Number 1
    • No2 = Number 2
    • No3 = Number 3

    Required result in the query:

    • ID1; IDa; No1
    • ID1; IDa; No2
    • ID1; IDa; No3
    • ID1; IDb; No1
    • ID1; IDb; No2
    • ID1; IDb; No3
    • ID1; IDc; No1
    • ...
    • ID3; IDc; No3

    In SQL this could be done with the select statement:

    SELECT *
    FROM TABLEBASE
    CROSS JOIN TABLE1
    CROSS JOIN TABLE2

    Sebastian

  • Martin Dráb Profile Picture
    Martin Dráb 231,321 Most Valuable Professional on at
    RE: Cartesian product of three tables via Query and QueryBuildDataSource

    Ergün is right - use relations(true) to use table relations, or addLink() if relations can't be used.

    By the way, please use Insert > Insert Code (in the rich formatting view) to paste source code. It's then easier to read. For example:

    Query                   queryAll  = new Query();
QueryBuildDataSource    qbds_base  = queryAll.addDataSource(tableNum(TableBase));
QueryBuildDataSource 	qbds_table1 = qbds_base.addDataSource(tableNum(Table1));
qbds_table1.joinMode(JoinMode::InnerJoin);

QueryBuildDataSource qbds_table2 = qbds_base.addDataSource(tableNum(Table2));
qbds_table2.joinMode(JoinMode::InnerJoin);

QueryRun qr = new QueryRun(queryAll);

while (qr.next())
{
	TableBase tableBase = qr.get(tableNum(TableBase));
	Table1 table1 = qr.get(tableNum(Table1));
	Table2 table2 = qr.get(tableNum(Table2));
}

  • Suggested answer
    ergun sahin Profile Picture
    ergun sahin 8,816 Moderator on at
    RE: Cartesian product of three tables via Query and QueryBuildDataSource

    Add relations (qbds.relations(true) or manuel relation)

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Tip: Become a User Group leader!

Join the ranks of valued community UG leaders

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,516 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,321 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans