.leftjoin, .innerjoin.
Joining Products and Product Extension Costs tables.
Always join by index fields: Primary Key or Business Key.

Code

def qapi = api.queryApi()

def p = qapi.tables().products()
def c = qapi.tables().productExtensionRows("Costs")

def today = new Date()

return qapi.source(p, [p.sku(), p.label], p.sku().equal("10000001"))
        .leftOuterJoin(c, { cols -> [c.Cost, c.Currency, c.SalesOrg, c.ValidFrom, c.ValidTo] },
                { cols ->
                    qapi.exprs().and(
                    c.sku().equal(cols.sku),
                    c.ValidFrom.lessOrEqual(today),
                    c.ValidTo.greaterOrEqual(today),
                    )
                })
        .stream { it.collect { it } }

Result

sku	label	Cost	Currency	SalesOrg	ValidFrom	ValidTo

sku	label	Cost	Currency	SalesOrg	ValidFrom	ValidTo
10000001	Spare part 10000001	49	GBP	0001	2024-12-01	2025-12-31
10000001	Spare part 10000001	78	GBP	0002	2024-12-01	2025-12-31
10000001	Spare part 10000001	1500	CZK	0003	2024-12-01	2025-12-31
10000001	Spare part 10000001	74	USD	0004	2024-12-01	2025-12-31
10000001	Spare part 10000001	1440	CZK	0005	2024-12-01	2025-12-31

Join a Table

Code

Result

See Also