Set up table three like this:
Make all three fields the primary key. Alternatively, make no field the primary key and create a rowKey instead. But let's focus on option #1, which will likely be more performant.
Consider table three "products by order" .
Intuitively, here the combination of Person, Order and Product defines a unique row, not just the combination of Person and Order.
With this change, your existing query should list multiple rows for each user and order in table three.
You first user's products should be represented like this:
Do not bother combining the Ids into one field. You can work on separate rows as well.
In your email, make use of the LookupRows function to get multiple rows to work in an email.
This will give you all rows for the user in question as a rowset. Possibly you'll have to also limit the lookup by OrderId.
Maybe you'll have to use lookupOrderedRows to sort by the latest order, limit the results to three or similar.
Just understand that without further limitation, all products from several orders will show for the user. This is the case in the code example below.
With your rowset, iterate through each line in a for loop and print the ProductId plus a ",". Afterwards, remove the last comma.
SET @productsByOrderAsRowset = LookupRows('products by order','subscriberKey',_subscriberkey) /* you might want to add 'orderId' to the lookup here)
SET @rowCount = rowcount(@productsByOrderAsRowset)
SET @resultList = ""
IF @rowCount > 0 THEN
FOR @i = 1 TO @rowCount DO
SET @row = row(@productsByOrderAsRowset,@i) /*get row based on loop counter */
SET @productId = field(@row,1)
SET @resultList = Concat(@resultList,@productId, ",")
/* trim last comma from the list of products*/
SET @length = length(@resultList)
SET @resultList = Substring(@resultList,1,subtract(@length,1)))
this should show: