How to sort a binding source with joins?

Question

I have a table called Tenants, and it has a field called PropertyID and UnitID.

I also have a binding source for this table (Tenants), however I want it sort it, but the values I want to sort it by are not in the table itself.

I want to do something like this:

SELECT t.*, p.PropertyName, u.UnitNumber FROM Tenants t
LEFT JOIN Properties p ON t.PropertyID = p.ID
LEFT JOIN Units u ON t.UnitID = u.ID
ORDER BY p.PropertyName, u.UnitNumber

How can I do this with the binding source?

Answer 1

create a view with this query, and bind to that.