In mORMot, all the methods available to handle many-to-many
ManySelect, DestGetJoined...) are used to
retrieve the relations between tables from the pivot table point of view. This
saves bandwidth, and can be used in most simple cases, but it is not the only
way to perform requests on many-to-many relationships. And you may have several
TSQLRecordMany instances in the same main record - in this case,
those methods won't help you.
It is very common, in the SQL world, to create a JOINed request at the main "Source" table level, and combine records from two or more tables in a database. It creates a set that can be saved as a table or used as is. A JOIN is a means for combining fields from two or more tables by using values common to each. Writing such JOINed statements is not so easy by hand, especially because you'll have to work with several tables, and have to specify the exact fields to be retrieved; if you have several pivot tables, it may start to be a nightmare.
Let's see how our ORM will handle it.