Hi @WLS-DM,
sorry for the late reply.
It turns out the Joins were working properly.
Quicksight was ignoring records from first table I was trying because of corrupted data in one of the fields (for reference a timestamp containing invalid dates).
I was joining that table with the custom query with a left join, hence the nulls for the joined columns.
This is why the preview of the custom query was giving me the records I expected, but the final dataset didn’t have those records, because skipped buy the left join.
That being said I reached support and it is indeed recommended to join tables from the same schema with a custom query, so thanks for your insight.
Sorry for the misleading question. Wish I could change the title.
Cheers,
Thomas