So I managed to come up with a solution, but … I’m not sure I can explain it’s behavior. I think I may have possibly hit an edge case bug in the ‘tuple item’ inspector, I’m not sure whether to rely on this behavior. Maybe @brolly33 or @alanm could comment…
I start off by casting each data to a string, so this only works with properties that can be represented as simple strings. I start by building a set of data1 and a set of data2:
(
set of (it as string)
of (
"1","Property1","Property2","Property3"
;"2","Property1","Property2","Property3"
;"3","Property1","Property2","Property3"
;"4","Property1","Property2","Property3"
)
, set of (it as string)
of (
"2","Property4","Property5","Property6"
;"4","Property4","Property5","Property6"
)
)
From there, I plan to unwind the first set, so everything in the “left side” of the left join has its elements expanded. I preserve the second piece of data as a set that I would iterate in the next step…
(
elements of item 0 of it
, item 1 of it
)
of
(
set of (it as string)
of (
"1","Property1","Property2","Property3"
;"2","Property1","Property2","Property3"
;"3","Property1","Property2","Property3"
;"4","Property1","Property2","Property3"
)
, set of (it as string)
of (
"2","Property4","Property5","Property6"
;"4","Property4","Property5","Property6"
)
)
Now, here’s where it gets a bit odd. Starting with the query above, I’m going to take the current “left side” element and compare it with all of the “right side” elements. This step will discard the entire row where there is no match in the right side. I expected that, and that I would handle cases where the right side doesn’t exist later. This part behaves as expected -
(
item 0 of it
, (item 0 of it, elements of item 1 of it) whose (tuple string item 0 of item 0 of it = tuple string item 0 of item 1 of it)
) /* of everything else so far */
Result:
( 2, Property1, Property2, Property3 ), ( ( 2, Property1, Property2, Property3 ), ( 2, Property4, Property5, Property6 ) )
( 4, Property1, Property2, Property3 ), ( ( 4, Property1, Property2, Property3 ), ( 4, Property4, Property5, Property6 ) )
This is as expected - the “1” and “3” have been discarded, because there was no 1 or 3 to match in the second set. However, I went ahead and started building the string tuple representations for the elements that are present…only to find 1 and 3 return as a result of my text conversions…
(
item 0 of it
, concatenation ", " of (it as string)
of tuple items whose (index of it > 0) of items 1 of
(item 0 of it, elements of item 1 of it) whose (tuple string item 0 of item 0 of it = tuple string item 0 of item 1 of it)
)
of
(
elements of item 0 of it
, item 1 of it
)
of
(
set of (it as string)
of (
"1","Property1","Property2","Property3"
;"2","Property1","Property2","Property3"
;"3","Property1","Property2","Property3"
;"4","Property1","Property2","Property3"
)
, set of (it as string)
of (
"2","Property4","Property5","Property6"
;"4","Property4","Property5","Property6"
)
)
The result for this, which brings back the 1 and 3 items from the first set, is
( 1, Property1, Property2, Property3 ),
( 2, Property1, Property2, Property3 ), ( Property4, Property5, Property6 )
( 3, Property1, Property2, Property3 ),
( 4, Property1, Property2, Property3 ), ( Property4, Property5, Property6 )
I did not expect this. I haven’t been able to explan how the “1” and “3” rows have returned, where item 1 still appears to be Nothing.
Finally, to combine them into a single string for each row, I’m doing
/* combine the "left-side" and "right-side" columns into a merged row */
(concatenation ", " of (item 0 of it ; item 1 of it)) of
(
/* the "left-side columns" of this emulated left-join */
item 0 of it
/* Find the "right-side columns" of this emulated left-join */
, concatenation ", " of (it as string) of
/* from the "right side of the left join", keep only the elements with index greater than 0; here tuple item 0 is our "primary key" column, already present above, and not to be repeated in the final */
tuple items whose (index of it > 0) of
/* from the match between item 0 ("left set") and matching elements from item 1 ("right set"), keep only the element from the right set */
items 1 of
/* find the elements from the second set, where the first item, ie "column", matches this item */
(item 0 of it, elements of item 1 of it) whose (tuple string item 0 of item 0 of it = tuple string item 0 of item 1 of it)
)
of
(
elements of item 0 of it
, item 1 of it
)
of
(
/* build the "left set" of the left join */
set of (it as string)
of (
"1","Property1","Property2","Property3"
;"2","Property1","Property2","Property3"
;"3","Property1","Property2","Property3"
;"4","Property1","Property2","Property3"
)
/* build the "right set" of the left join */
, set of (it as string)
of (
"2","Property4","Property5","Property6"
;"4","Property4","Property5","Property6"
)
)
which results in
1, Property1, Property2, Property3,
2, Property1, Property2, Property3, Property4, Property5, Property6
3, Property1, Property2, Property3,
4, Property1, Property2, Property3, Property4, Property5, Property6
** by the way, I’m running this in a 9.5.8 debugger because that’s what I had handy at the time. I haven’t upgraded the debugger on my home PC in quite a while 
