Search code examples
azure-data-explorerkql

How to Project Rows Whose Column Values Only Appear in the First Table?

I have two Kusto tables in the same database, Open_Work_Items and Closed_Work_Items that appear respectively like so:

Item ID  | Opened Date
1234     |  <DateTime>

Item ID  | Closed Date
1234     | <DateTime>

My issue is that I cannot remove work items from Open_Work_Items once the Item ID appears in Closed_Work_Items, but I would still like to query which work items are open. This means I need to find distinct Item IDs Open_Work_Items that do not appear in Closed_Work_Items, but I do not know which Kusto function(s) I can use to do so.

I've looked at Tabular and Scalar Operators, but I'm not understanding how I can combine them to get what I want here. Any help/advice would be appreciated!

Any help would be appreciated!

Solution

  • Needed to perform left anti-join:

    https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/joinoperator?pivots=azuredataexplorer#left-anti-join-flavor