kusto how to write subquery

Question

problem: for each row in a table (from analytics table) I am trying to run a subquery to find the corresponding row in a second table (from externaldata). I think I want a subquery but there maybe a better option. there is no column linking each table so I cant use join, the only relationship is that the numbers from the analytics table may be between a start and end number in an externaldata table.

let IDlist = datatable(value:long)
[
45,
76,
150,
202,
2156,
3004,
5001,
];

// imported from externaldata
let idlookup = datatable(start:long, end:long, name:string)
[
1,100,"bob",
101,105,"susan",
200,1000,"henry",
5000,5004,"clair",
];

//using between doesnt work
idlookup
| where idlist between (start .. end)
| project idlist, name

expected output
45 - bob
76 - bob
150 - no match
202 - henry
2156 - no match
3004 - no match
5001 - clair

The query above doesn't work as expression before the between statement is not a scalar expression. I hoped a subquery would solve this but I cant get figure out the syntax. Any help is much appreciated.

Answer 1

Here is one way:

let IDlist = datatable(value:long)
[
    45,
    76,
    150,
    202,
    2156,
    3004,
    5001,
];
// imported from externaldata
let idlookup = datatable(start:long, end:long, name:string)
[
    1, 100, "bob",
    101, 105, "susan",
    200, 1000, "henry",
    5000, 5004, "clair",
];
IDlist
| extend dummy = 1
| lookup (idlookup | extend dummy = 1) on dummy
| where value >=start and value <= end
| join kind=rightouter (IDlist) on value
| extend name = iff(isempty( name), "no match", name)
| project name, value = value1