Angular Backend - Query Data From Multiple Tables

Hello,

i am currently developing an custom endpoint where  i want to query data from multiple tables (via Join) and then return a custom class with this data populated.

Something like "SELECT * FROM TABLE Person INNER JOIN PersonInDepartment  d ON d.UID_Department = Person.UID_Department"

Does anyone have an idea how this can be achieved? (we are using role based auth)

I followed the examples from the SDK but there are only queries for one table. (see the code below)

Is there somethign like .WithJoin or do  i need a predefiend SQL?

using QBM.CompositionApi.Definition;
using QBM.CompositionApi.Handling;
using QER.CompositionApi.Portal;
using System;
using QBM.CompositionApi.Crud;
using VI.DB;
using VI.DB.Entities;
using VI.Base;

// Declaration need by One Identity
//[assembly: QBM.CompositionApi.PlugIns.Module("CCC")]

namespace CCC.CompositionApi.Server.PlugIn
{
    public class CustomCompositionApi : IApiProviderFor<PortalApiProject>
    {
        public void Build(IApiBuilder builder)
        {
            builder.AddMethod(Method
                // Define the URL to access this API endpoint
                .Define("ccc/test")

                // Define the database table/entity from which the data should be read
                .FromTable("Person")

                // Allow reading the data (GET request)
                .EnableRead()

                // Assign a description to the API end-point
                .WithDescription("Returns all external identities for the specified manager, if not defined all external persons are returned")

                // Define a restriction for the data query
                .WithWhereClause(
                    delegate (IRequest request)
                    {
                        ISession session = request.Session;
                        ISqlFormatter f = session.SqlFormatter();

                        string uidPerson = session.User().Uid;
                        string whereClause = String.Empty;

                        whereClause = f.AndRelation(
                            f.Comparison("IsInActive", false, ValType.Bool, CompareOperator.Equal),
                        );

                        return session.Variables.Replace(whereClause);
                    }
                )

                // Define the columns which should be returned (for all use `WithAllColumns()`)
                .WithAllColumns()
     

                // Enables the datamodel endpoint to allow the client to query data model information
                .With(m => { m.EnableDataModelApi = true; })

                // Enable exporting to various formats depending on the "Accept" HTTP header.
                .AllowExport()
            );

        }
    }
}

(By Creating this question an exception occured, if this question is duplicated)

Best regards

Armin