Do you have a sample database to share with your specific schema and some sample data?  It can be easier to build up a complex query by starting with a simpler one and adding parts and complexity gradually.

To get the total balance for each order you need an aggregate query over the payment amounts for each order, presumably with a LEFT JOIN of Order and OrderPaymnt on the OrderId and also GROUP BY the OrderId.  That can give you the total paid, and you already have the original total for the order.

To include other details from each order in the aggregate query you would have to add them to the ORDER BY columns (a cheat which can work in some cases, but be careful).  That should be safe in this case because OrderId is presumably the PRIMARY KEY for Orders, so once you GROUP BY that column any other columns of Orders would be the only possibly value under that OrderId value.

SELECT o.OrderId, o.OrderTotal, Sum(p.Payment) AS "TotalPayment", Max(p.PaymentDate) AS "LatestPayment"
FROM Orders o
LEFT JOIN OrderPayments p ON p.OrderId = o.OrderId
GROUP BY o.OrderId, o.OrderTotal

This query doesn't yet compute the balance owed, but the pieces are there from which to compute it.

But to add the additional parts could start to get messy.  Another trick to build a more complex query without breaking the simple structure needed for this aggregate query is that you can use a sub-query as the FROM table.  VistaDB handles that pretty well (when it's the FROM table) because the sub-query is simply executed and subsequent joins flow from it.  (It's not so good use a sub-query as a joined table because it may try to execute the sub-query numerous times and it won't join as efficiently.)

SELECT o.*, b.TotalPayment, b.LatestPayment, o.OrderTotal - b.TotalPayment AS "BalanceOwed"
FROM
(
    SELECT Orders.OrderId, Sum(p.Payment) AS "TotalPayment", Max(p.PaymentDate) AS "LatestPayment"
    FROM Orders
    LEFT JOIN OrderPayments p ON p.OrderId = Orders.OrderId
    GROUP BY Orders.OrderId
    ORDER BY Orders.OrderId  --This is legal in VistaDB, but ORDER BY might not be allowed here in SQL Server.
) AS b --Need an alias for the sub-query, so call it b for balance, for example.
JOIN Orders o ON o.OrderId = b.OrderId

This uses the sub-query-as-FROM-table trick and wraps it in a JOIN to bring in the rest of the Orders table (It specifies o.* but you can specify whatever specific columns or expressions are needed). You can also LEFT JOIN your OrderDetails table if desired, although it would mean that the values from Orders and from the sub-query would be duplicated for however many rows of OrderDetails each specific order has, so that might be a weird query.

This is an example, but it may not be quite what you want.  It doesn't account for the previous balance, for example.  It's not clear if what you need is an ordered ledger with each payment adjusting the previous balance for the new payment and keeping a running total.  There may be a way to do that in SQL Server, but VistaDB doesn't support some features that might be needed to do that in a query directly.

    -Rob

I saw that only after I'd just posted.  I guess what that query solves is giving you all of the specific most-recent-payment entry (which an aggregate query can't) if that's what you actually needed.  That's a little different than what I thought you were going for, but I can now see how it fits with what you were saying at the end of the first post.

It looks like it's joining in a correlated sub-query (which I've tried to experiment with in joins before without success, but maybe it's only legal in a CROSS JOIN and not in INNER or OUTER joins which have ON clauses?).  VistaDB accepts correlated sub-queries (meaning it references a table outside the sub-query) in a WHERE clause, but I don't know whether it will allow them in a CROSS JOIN like this (but if you're using SQL Server then that doesn't matter for you).  I'll have to test that sometime because it can be a handy construct.

In this case, if VistaDB accepts it, it could also be an exception to my general advice against using a sub-query as a joined table because unlike using a non-correlated sub-query as a joined table, the WHERE clause within the correlated sub-query will filter the sub-query efficiently (given an index on OrderId) for each necessary execution (for each OrderId value).

    -Rob