I want to lookup values from a different query. I stumpled upon two keywords which may could make sense: join and lookup. leftsemi join looks exactly what I need, but I cannot access columns on the right side. Any other variants that I've tried are adding more rows. The resulting table should not have more entries than FactTable, but can be less in case of null values.

let FactTable=datatable(name:string,timeprofile:string) [
  "Paul", "10:30",
  "Eric", "8:30",
  "Eric", "9:30",
  "Petra", "9:49"
];
let DimTable=datatable(name:string,details:string) [
   "Paul", " ",
   "Eric", "-",
   "Eric", "-",
   "Eric", "-",
   "Lessly", "  ",
   "Martha", " ",
   "Martha", " ",
   "Martha", " "
];
FactTable
| join kind=leftsemi DimTable on name 

But the resulting table is missing details column from DimTable.

CodePudding user response：

1. Performance wise it's recommended to Join when left side is small and right side is large
2. Once you switch the order of the tables, the default Join kind, innerunique, does exactly what you need - inner join with duplicates removal from the left side

DimTable
| join kind=innerunique FactTable on name 

or simply

DimTable
| join FactTable on name 

Fiddle