Skip to main content
Question

Showing all combinations of two child tables in Celonis PQL

  • April 14, 2026
  • 3 replies
  • 382 views
wolfgang.voelk
Level 8
Forum|alt.badge.img+2

Hi Community,

I am struggling to reproduce a classic SQL join pattern in Celonis PQL and would appreciate some guidance.

 

### Data Model

I have an **N:1:M relationship** between three tables:

- **Table A** – Material (1 record per material)

- **Table B** – SalesOrg-related attributes 
  - N records per material 
  - Foreign key: `B.material_id → A.id`

- **Table C** – Plant-related attributes 
  - M records per material 
  - Foreign key: `C.material_id → A.id`

 

### What I want to achieve

For each material, I want to display **all possible combinations** of SalesOrg and Plant, i.e. the cartesian product of tables B and C per material.

 

### SQL reference (works as expected)

In SQL I would do something like:

SELECT  A.id,  B.org, C.plant
FROM A
LEFT JOIN B ON A.id = B.material_id
LEFT JOIN C ON A.id = C.material_id

Problem in Celonis / PQL
I simply do not manage to reproduce this in a View via PQL. In particular, I fail to get three columns in a table view (material, org, plant) showing all combinations of B and C for each A.
 

Any hints or best practices would be highly appreciated.
Thanks in advance!

    3 replies

    Jan-Peter van der Steege
    Level 12
    Forum|alt.badge.img+27

    Hi ​@wolfgang.voelk, 

    Since you do not have to define joins in PQL (you do that already in your data model), and you are fine that your table explodes (i.e. no aggregations needed), I would assume that if you add a table component, and add each field in a different column, Celonis handles this automatically. 
    If not, you can use the BIND function, to bind your 1-side to the N-side, making it a N:M join. 
    If this still doesn’t work, please share the error message you see. 

    Best regards,

    Jan-peter

    Visit Our Website: https://www.marcadus.com/en/services/process-intelligence/
    wolfgang.voelk
    Level 8
    Forum|alt.badge.img+2

    Hi @janpeter.van.d!

    Simply putting all columns in one table does not work. There I get the “usual” error:
    No common table could be found. The tables ["C"] and ["B"] are connected, but have no common table. This means that they do not have a direct (or indirect) 1:N or N:1 relationship. Join path: [C]N <-- 1![A]!1 --> N[B].

     

    ...and it’s with the BIND in this context, that I absolutely get my wires crossed with. I do not find the right combination for this. What column to bind at which table?

     

    Best regards,

    Wolfgang 

    Jan-Peter van der Steege
    Level 12
    Forum|alt.badge.img+27

    Hi ​@wolfgang.voelk, 

    I did a bit deep diving, an think this is impossible by design, see https://docs.celonis.com/en/join-functionality.html#id654353. Celonis allows only a single join path between tables, disabling explosions of tables. 
    Even with BIND (where you could embed Materials (A) to the SalesOrg table (B)), you would have a N:M relationship, that doesn’t work. 

    Only solution would be using PU-aggregations, for example, using PU_STRING_AGG, to add all the Plant-related attributes as a List to a single row of your B-table. However, it’s likely that this is not the solution you’re looking for…

    An escape is to do this in the Data Integration section with SQL, and add a new Object/Table to your Data Model.

    Best regards,

    Jan-peter
     

    Visit Our Website: https://www.marcadus.com/en/services/process-intelligence/
      Community Code of ConductAccessibility statement