The Query Object is often overlooked in Business Central, but it does give SQL’ish access to creating queries, check it out:

https://youtu.be/v609PWAUnxg