To be totally clear, perhaps it will help in future before I even bother sharing a strategy, I have usually done dozens of exploratory research and Proof of concepts for all the elements a believe are needed. They are often disembodied from the larger strategy so I would not bring them in as details when explaining the concept.
- If you can contribute to the concept please do so, otherwise I look forward to your contribution as the details unfold. It is the diversity in the way we think that makes collaboration so powerful.
For example previous work to build dynamic tables from scratch with both row and column filters, my flags solution, and advanced nested lists all comprise the code we would need.
In your SQL Example above I can visualise this in nested lists, however with the extended filter runs and functions I can visualise moving the nested lists into functions. So we then can have the equivalent of SQL in Tiddlywiki filters. By keeping your example in mind I can visualise an “interpreter” to translate SQL into Filters, although I know there are gaps in my understanding there, I have seen plenty of equivalents including tiddlywiki’s scripts parsing into html, or graph plugins etc… to know it is possible.
I agree whole heartedly with this however part of playing with this 5.4.0 “multi-valued variables” feature for me has been seeing how it can be used in some of the “less tractable” cases, at least when it comes to moving multi-value and keyword value lists into filters.
- In the next section you can see how I would like to generate filter output, a row, that may contain two or more titles which may include duplicates of one of the titles, that represents the keys in joined tables. I am not sure if multi-valued variables will help here, but I expect so.
- for example the initial output of a join would be;
key1oftable1, key5oftable2
key2oftable1, key7oftable2
key2oftable1, key9oftable2
A “key” 
learning here for me
Whilst trying to pencil out the concepts here I have come to discover at least one way to “substantially” simplify multiple table manipulation in tiddlywiki.
With the exception of list fields or fields with a title (key), or a field that is the basis of a selection filter (even then) the following is true;
All the other columns in all other tables can be ignored while doing a query, that is operations like select and join are driven by the keys/titles in the tables, not the values in other field/columns. Once you have these you can then return to each named tiddler to retrieve the various field/column values to generate the final “virtual table” or record.
Another simplification is;
If the title of the current tiddler is the key to its entry in another table, sometimes all we need to know is “this key may have an entry in another table (as a foreign key)”, thus we can know it can be joined. As a result there may be value in asking the designer (once off) to nominate which tables have a relationship with another and the fieldname in which it is stored, including if it is a list field.
- This could be the point to “generate” a set of custom operator/functions which can then be used to do the joins and other queries. an example may be storing these in a tiddler table1.table2 which can automatically generate this joined table, in that tiddler (with/without a filtered selection). In the real world this may read
client.invoices - It would also generate functions to filter the output, that is, a query against this virtual table (but only in memory. Perhaps we store these in table1/global.
Gven a list of joined rows
So far the output of a join has being described as totally in memory but we can use a method as documented here How to have your code and action it too - batch operations on multiple tiddlers, refactoring your wiki to add actions to store the output a temporary, or permanent (as at) record eg in a tiddler.
- This still requires a trigger so I hope one day we have an action on navigate (once) to trigger this.